-
The strict definition of a SQL Server View is a virtual table that presents table(s) data. Using the sp_refreshView – updates the metadata of the the view. Any view depends on underlying objects – and from time to time these objects are changed. Typically this is useful when a column is added...
-
When you are dealing with SQL Server performance issues, not every issue can be classified as a problem for SQL Server. There are common characteristics within the issues, such as: Ad-hoc query execution typical taking longer time. Intermittent High CPU. Blocking irrespective of slow performance. Few...
Posted to
SQLMaster
by
Satya Jayanty
on
10-04-2010
Filed under:
Filed under: sql server, #SQL Server, performance, monitoring, #TSQL, BRH, compilation, execution plan, execution, plan guide, stored procedure
-
PROFILER is very handy tool to trace the events on SQL Server, like wise SQL Server 2008 gets the new feature Extended Events which is a light-weight & less intrusive tool. Still there are number of DBAs out there are really scared to invoke PROFILER...
Posted to
SQLMaster
by
Satya Jayanty
on
09-21-2010
Filed under:
Filed under: best practices, sql server, #SQL Server, performance, free space, profiler, mvp, version, trace, performance monitoring, BRH
-
If your corporate environment is monitored by System Center Operations Manager 2007 then here is the update to download the Monitoring Management Pack. This pack of SQL Server Management Pack provides the capabilities for Operations Manager 2007 SP1 and R2 to discover SQL Server 2005, 2008, and 2008...
Posted to
SQLMaster
by
Satya Jayanty
on
09-20-2010
Filed under:
Filed under: best practices, dba, sql server, #SQL Server, performance, sql server 2008 r2, knowledge, monitoring, mom, tuning, #DBA, BRH
-
Performance Monitor a.k.a perfmon is used to monitor different counters for different purposes. However, some counters are bit confusing. Process and Processor two misleading counters so thought of putting this note. Processor When adding processor object, you have _Total object and 0, 1 .. which are...
-
This post is inspired by a discussion at MSDN forums, which is about the Performance effect of Cast,Convert and Year() functions on Datetime columns in where clause. In MSDN forums, recently saw a question, “how to retrieve the records from a table having the Year 2005?” and found the below reply using...
-
If you are a programmer working with SQL Server, you must have found it little embarrassing to display information which spans across multiple pages (web pages). SQL Server 2000 did not allow you to retrieve a specific range of records, say, records 51 to 100 ordered by a certain column. For example...
-
This white paper describes how application developers can apply query and processing performance-tuning techniques to their SQL Server 2008 Analysis Services OLAP solutions. Because Microsoft SQL Server Analysis Services query and processing performance tuning is a fairly broad subject, this white paper...
-
Every so often on the on-line forums, the question of : Which is better Count(*) or Count(1) ? will occur. So can it categorically be proved than one is better than the other ? What we need to do is populate a table and use both count(*) and count(1) and see if we get any performance difference...
-
I am somewhat uncomfortable blogging this, as I feel that it falls into “Polish a turd” category. Having said that, it is a particularly ugly turd and it does polish up rather well. Though as the saying goes, it is still a turd. The evils and inadequacies of UDF (User Defined Functions) in...
-
Switching from using a CURSOR to a WHILE loop will mean they are not processing by a row. This is not true. The following examples illustrates this myth. Both examples are processing row by row: Example 1 - CURSOR create table #mytemp (ID INT , avalue VARCHAR(100), status INT ) INSERT INTO #mytemp (ID...
-
Recently on the SSC forums, someone asked about how to measure the money in an actual dollar figure that they have saved the company with their performance tuning. I've often struggled with this myself, and so I started thinking about it a bit deeper. I started to respond in the thread;...
-
I have received a number of requests for an explanation of my winning query of TSQL Challenge 19 . This involved traversing a hierarchy of employees and rolling a count of orders from subordinates up to superiors. The first concept I shall address is the hierarchyId , which is constructed within the...
-
Glenn Berry ( Blog ) writes a lot of queries to extract information from the system DMV’s. One of them in particular I found extremely helpful in fixing some of the issues in my system. I took his query (the CTE at the top) and added some text manipulation to actually generate the create...
-
During his 24 Hours of Pass session, Andy Leonard was asked about the difference between a IsNull and a coalesce operation. Ordinarily, and let’s assume we are only talking about 2 parameters here, there isn’t much difference between them. Certainly you could probably run a million row query...