Finding Potential Bottlenecks with DMVs.

July 28, 2010

The following query is a great way to get a look into how your stored procedure driven application is performing. I wouldn’t recommend running this during your peak hours. What I have done is scheduled it to run in the off hours and store into a table in my dummy DB. I then query that table as it is less intensive then quering the DMVs. What this query shows me is area’s a I need to tune within my application. If a query is executed many times and has a high number of logical reads perhaps my index needs to be tuned. Same goes if I look into area’s where physical data processing is happening… If your data isn’t being found in the cache you may have a bottleneck or perhaps you are just pulling to much data. It’s also great to see which queries are executed most frequently as there is always room for improvement and it’s important to understand how your data is being accessed.

select
substring(text,patindex(‘%create %’,text),50),
 SUBSTRING(st.text, (qs.statement_start_offset/2) + 1,
    ((CASE statement_end_offset
        WHEN -1 THEN DATALENGTH(st.text)
        ELSE qs.statement_end_offset END
            – qs.statement_start_offset)/2) + 1) as statement_text,
db_name(dbid) DB,
creation_time,
last_execution_time,
execution_count,
(total_elapsed_time/execution_count)/1000000 as time
,total_physical_reads
,last_physical_reads
,min_physical_reads
,max_physical_reads
,total_logical_writes
,last_logical_writes
,min_logical_writes
,max_logical_writes
,total_logical_reads
,last_logical_reads
,min_logical_reads
,max_logical_reads
,getdate()
FROM sys.dm_exec_query_stats as qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) as st
where db_name(dbid) =’YourDB’

Leave a Comment » | Indexes, Monitoring, Performance | Tagged: , , , , , , , | Permalink
Posted by motherofallgeeks

Index This Index That

December 30, 2008

So let me ask you this… Do you really know how SQL Server storage engine works? You would be surprised how many people can’t answer simple questions about indexes on interviews. Sorry Folks if you can’t tell me what the difference between and clustered and a non-clustered index your interview score just went down.

That being said this is a very simple question I like to ask people on interviews. If your going to rebuild the indexes on a large table which has no Forgein keys. How would you do it and why? You cannot use any  dbcc or online index operations. We are going old school. To keep it simple assume your table is 5GB, has a clustered index and 5 Non-clustered indexes some of which are composite.

When I ask this my goal is to find out if you truly understand how the data is stored. If you do then the answer is quite easy.

I’ll give you the answer I am looking for first then I will explain why.

“You would drop the NON-Clustered indexes first. Then drop the Clustered index. Next you would create the CLustered index then the Non-CLustered indexes.”

Now the why.

A clustered index in the simplest explaination is that it is your data. A classic example of a clustered index is a phone book. Now of course we can get into B-Tree structures but lets save that for another post. That being said If I drop my clustered index first the operation will also drop and recreate my non-clustered indexes and can potentially take alot of time. To speed up the process drop the NC indexes first. (AFTER YOU SCRIPTED them of course)

Leave a Comment » | Indexes, Maintenance, Performance | Tagged: , , | Permalink
Posted by motherofallgeeks