I will be speaking at the Greater Fort Worth SQL Server Users group this Wednesday. The talk will be on Troubleshooting with the DMV’s. If you are a DFW SQL’er come on out. Directions and more info can be found here.
If you don’t come, I am sending my boy after you with blow torch and a pair of pliers.
Wait stats analysis is a great skill to have in your arsenal. There are lots of tools out there already. I also suggest you read Tom Davidson's whitepaper on it if you need background on this topic. There was a niche missing in my toolbox. You see, these stats are cumulative since the instance has started or you clear with DBCC SQLPERF(WAITSTATS, CLEAR).
Whatever sys.dm_os_wait_stats reports as the highest waiter may have happened last week. If you want to know what is hammering the server now, you can look at several different DMV's like sys.dm_exec_requests but that data is transient. My rough code (all my code is rough) below will tell you what the server has been waiting for in the last second of time. It is definitely a firefighting "WTF is going on right now" query. It would also complement this query when fighting fires.
Read the whitepaper for a better explanation but, basically, there are two buckets of waits. Signal waits which are actual waits on CPU execution time. The others are resource waits which means SQL is waiting on other stuff like a locks, latches, log writes, memory etc. Again the whitepaper does a great job correlating these sometimes cryptic names with resources.
Lastly, analyzing wait types goes hand and hand with perfmon. For example, you have both high CPU and high IO in perfmon. This script can help you see what SQL is actually waiting on the most so you can tackle that problem first.
SELECT wait_type ,signal_wait_time_ms AS 'CPU' ,wait_time_ms - signal_wait_time_ms AS 'Resource' INTO #temp FROM sys.dm_os_wait_stats WHERE wait_time_ms <> 0 WAITFOR DELAY '00:00:01' SELECT a.wait_type , signal_wait_time_ms-cpu AS CPUDiff , (wait_time_ms - signal_wait_time_ms)-[resource] AS ResourceDiff FROM sys.dm_os_wait_stats a JOIN #TEMP b ON a.wait_type=b.wait_type ORDER BY 2 DESC DROP TABLE #temp
It is fairly widely known that implicit conversions of data types can cause scans. However, you may not be able to control what goes in your database, you may have inherited an app and we just are not perfect.
On top of that some can be tricky. For example, did you know that scope_identity and @@identity both return numeric data types. An AVG of a SMALLINT column returns an INT.
Here is a simple repro which the same query does an index scan instead of an index seek
CREATE TABLE t1( id INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, c1 VARCHAR(100) ) GOINSERT INTO t1(c1) SELECT 1GO 10000INSERT INTO t1(c1) SELECT 0GO 10CREATE INDEX ix ON t1 (c1) CREATE PROC p1@blah NVARCHAR(100) ASSELECT id FROM t1WHERE c1 = @blahEXEC p1 N'0'CREATE PROC p2 @blah VARCHAR(100) ASSELECT id FROM t1WHERE c1 = @blahEXEC p2 '0'DROP TABLE t1DROP PROC p1DROP PROC p2
I found a sample query from Umachandar Jayachandran and made some mod’s to it. It basically scans your procedure cache for index scans containing implicit conversions.
I have posted it at the SQLServerPedia Wiki here. It is the 2nd query listed.
Note: Implicit conversions may not always result in a scan.
BTW, did you know I am an editor for the TSQL code section over at http://sqlserverpedia.com/wiki/Transact_SQL_Code_Library. I would have announced it sooner but I hadn’t actual done anything yet with the job drama and holidays. Welp, the time has come. I have uploaded several DMV queries and I am about to go over the other submissions. On top of that, I will be participating in a web cast on it. We will get to that in a minute.
I was wondering how this whole wiki thing would work out. I mean every time I hear wiki, I think of Newcleus. Why not just post stuff here. Well, I will probably do that too when I run across something cool.
Here is what makes the wiki cool:
Here are the details about the webcast I mentioned:
ctrl+v via BrentO
On Thursday, December 11th at 8am Pacific, 11am Eastern, I’ll be doing a live video webcast with some of my fellow SQLServerPedia editors:Denny Cherry, Performance Tuning EditorGreg Low, Architecture & Configuration EditorJason Massie, T-SQL Code Library EditorWe’re going to talk about what we’re doing, why we’re doing it, and how you can help. I’ll even create a new Wiki article and edit an existing Wiki article in real time, in front of everybody, to show how it works and how easy it is. (Watch it not work, hahaha.)Sign up for the SQLServerPedia Webcast
On Thursday, December 11th at 8am Pacific, 11am Eastern, I’ll be doing a live video webcast with some of my fellow SQLServerPedia editors:
We’re going to talk about what we’re doing, why we’re doing it, and how you can help. I’ll even create a new Wiki article and edit an existing Wiki article in real time, in front of everybody, to show how it works and how easy it is. (Watch it not work, hahaha.)
Sign up for the SQLServerPedia Webcast
To quote Tim Ford, “I will be the dumbest guy in the room” :) It should be fun though!
I posted the first Captain VarChar(MAX) comic last Friday. It takes a swing at profiler. A lot of you are like “Duh!”. I have just done too many dba interviews where the candidate’s default answer is profiler. The interview usually gets tough or really short at that point. Just say no to SQL profiler.
I really have to stretch my imagination to come up with a situation where using SQL Profiler sounds like a good idea. For example, you quickly want to capture “ordered” events when repro’ing a problem in your *test* environment AND you filter it by your SPID. Any other time, you are wasting lots of time, cause performances overhead and probably missing what you are looking for while digging for the needle in the haystack. Also contrary to popular belief, running the GUI on another machine DOES NOT help much. For the rest of the post when I refer to a trace, I am referring to one captured with a server side trace unless I say “profiler” or “GUI”
I will admit that I have brought a production app to its knees with profiler. Picture this. You get a call while you are running profiler that the app is having issues. At first you think, “Hey, I have a trace running so at least I will be able to pinpoint the problem.” Then you see TRACE_WRITE wait types. Your stomach knots up because you realize that you just caused a production outage. Don’t be that guy(or gal).
SQL 2005+ just gives you too much info through the DMV’s. 90%+ of the time you can use the DMV’s instead of Profiler. The other 10% of the time, use a server side trace not the Profiler GUI. I have never seen a server side trace bring down an app. Even unfiltered traces on highly transactional systems where a gigabyte+ is collected every minute. I can see it causing an issue but the server would have to be pretty max’ed out to begin with.
Lastly, a trace only reports CPU, Reads, Duration etc when the query completes. Query completion for a misbehaving query could take 10x to 100x longer than normal. This makes traces unusable in a lot of cases when troubleshooting.
Let’s look at some scenario’s:
Troubleshooting a performance issue that is currently happening
A SQL trace in this situation is almost never needed on SQL 2005+. I troubleshoot like this:
1) See if\what hardware subsystem is pegged out with about 5 or 6 perfmon counters.
a) Verify with DMV data or wait stats if needed.
2) Identify the problematic queries with the sys.dm_exec_% DMV's
3) Fix.
Here is the query I use. In a nutshell, it grabs a snapshot, waits a second and does a diff so I can see what is currently slamming the server. Otherwise, the cumulative CPU and IO columns might throw me off. I also grab the query plan which is easy here but very expensive in a trace and hard to correlate with the SQL completed event.
select r.cpu_time
, r.logical_reads
, r.session_id
into #temp
from sys.dm_exec_sessions as s
inner join sys.dm_exec_requests as r
on s.session_id =r.session_id --and s.last_request_start_time=r.start_time
where is_user_process = 1
waitfor delay '00:00:01'
select
substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1 then datalength(h.text)
substring(h.text, (r.statement_start_offset/2)+1 ,
((case r.statement_end_offset when -1 then datalength(h.text)
else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text
, r.cpu_time-t.cpu_time as CPUDiff
, r.logical_reads-t.logical_reads as ReadDiff
, p.query_plan
, r.wait_type
, r.wait_time
, r.last_wait_type
, r.wait_resource
, r.command
, r.database_id
, r.blocking_session_id
, r.granted_query_memory
, r.reads
, r.writes
, r.row_count
, s.[host_name]
, s.program_name
, s.login_name
on s.session_id =r.session_id and s.last_request_start_time=r.start_time
full outer join #temp as t on t.session_id=s.session_id
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) h
cross apply sys.dm_exec_query_plan(r.plan_handle) p
order by 3 desc
drop table #temp
Proactive performance tuning
Now this starts to get in to fuzzy territory. Technically, a SQL trace is more thorough than doing your analysis off of the procedure cache. However, I would say benefits outweigh the losses in most but not all cases.
Procedure Cache Pro’s
· No need to capture a trace· No need to load a trace· Aggregation already exists so the data can be smaller assuming that the application has good plan reuse.
· No need to capture a trace
· No need to load a trace
· Aggregation already exists so the data can be smaller assuming that the application has good plan reuse.
· Easy access to XML Showplan
Procedure Cache Con’s
· Queries can be flushed until next execution for many reasons· Some queries are not cached. These are usually queries that you don’t care about.
· Queries can be flushed until next execution for many reasons
· Some queries are not cached. These are usually queries that you don’t care about.
To replace SQL trace analysis with a DMV query, use something like this:
select total_worker_time/execution_count as AvgCPU , total_elapsed_time/execution_count as AvgDuration , (total_logical_reads+total_physical_reads)/execution_count as AvgReads , execution_count , substring(st.text, (qs.statement_start_offset/2)+1 ,
((case qs.statement_end_offset when -1 then datalength(st.text)
else qs.statement_end_offset end - qs.statement_start_offset)/2) + 1) as txt , query_planfrom sys.dm_exec_query_stats as qscross apply sys.dm_exec_sql_text(qs.sql_handle) as stcross apply sys.dm_exec_query_plan (qs.plan_handle) as qp --where – filter as needed. --group by – Consider striping literals and grouping by the SQL text.
--See Inside SQL Server 2005 for more info. --Order by – Sort as needed.
Uncommon cases
There are some less common uses of SQL traces like the blocked process report, deadlock report, recompile tracing and many others. Make sure you review all of your options and don’t forget about the WMI Provider for Server Events. Use your best judgment and avoid the GUI like the plague.
Conclusion
To summarize very briefly: Never use the SQL Profiler GUI, know your tools, and pick the best one for the job.
So you are experiencing CXPACKET wait types? If you run a google search, you will quickly find out you are experiencing the dreaded SQL Server parallelism problem and you must reduce MAXDOP to 1. While that is possible, I say most likely not. I say that, most of the time, SQL is doing the best it can with what it has to work with. That is the query, the data and the schema.
Microsoft has come a long way since version <= 7.0 when it comes to parallelism. If your OLTP queries follow best practices and are well indexed, they probably will never generate a parallel plan. This is because they are fast and access a small amount of rows. If they are missing indexes or SQL overestimates cardinality, SQL might decide to do scans, sorts, hashes, spools etc. These iterators, among others, can go parallel to reduce execution time at the cost of system resources. These iterators are not bad and they do have their place. It just isn’t on OLTP type of queries most of the time.
So dropping the MAXDOP on an OLTP system to 1 probably won’t hurt much because most of the time there is an IO bottleneck. If the CXPACKET wait types are a symptoms of poor indexing and row count estimation, it won’t help either. Whole books have been written on indexing, query tuning and there is a nice whitepaper on stats best practices to avoid estimation problems so I am not going to go into that. I will give you some pointers on quickly identifying whether the CXPACKET is a symptom or the cause.
So your boss and boss's boss run over to your desk yelling about the customer complaints of slowness with SQL. You run this query or something similar:
select substring(h.text, (r.statement_start_offset/2)+1 , ((case r.statement_end_offset when -1
then datalength(h.text) else r.statement_end_offset end - r.statement_start_offset)/2) + 1) as text
You notice that you have several rows with CXPACKET wait types. With this query, you click the xml show plan link and: 1. search the xml for missing indexes. 2. Save as a .sqlplan and reopen in SSMS3. Compare estimated and actual rows in the iterators on the right side of the plan. Poor estimates may bubble to the left as well.4. If there are no missing indexes, estimates and actual are fairly close, reducing MAXDOP may help if it is not a huge report or query.
5. If there are missing index or bad estimations, fix it! :)
Of course, these are not rules set in stone. Just a style in the art of database administration. Just don't blame it on a "parallelism bug" because it is a poor musician that blames his instrument.
One thing to note, if you do turn down MAXDOP server-wide, turn in on at the query level(enterprise edition) on your index operation because they are optimized for it.Alter index all on tblBlah rebuild with (maxdop=32);
I believe this is a new feature RC0. If I missed it before, shame on me because this is awesome. My first 2 blog posts(Part 1 and Part 2) were on doing a similar things with query patterns. It is basically an additional column on sys.dm_exec_requests and sys.dm_exec_query_stats that hold a binary hash. The hash is same for queries that are the same or similar. For example, these queries should all have the same hash(disregard parameterization and trivial plans for simplicity):
select c1 from t1 where c2 = 1
select c1 from t1 where c2 = 2
But this query will have a different hash:
select c1 from t1 where c2 > 1
This allows you to group and aggregate similar queries by the hash. It is really useful in OLTP environments where the same query might only take milliseconds but execute 1000's of times a second. If you don't aggregate, those those types of queries may not show up on the radar but might be responsible 90% of the CPU usage. Shaving a couple of cycles of CPU off of one of those and server-wide utilization drops.
Here is a nice sample from BOL.
1:SELECT TOP 5 query_stats.query_hash AS "Query Hash",
2:SUM(query_stats.total_worker_time) / SUM(query_stats.execution_count) AS "Avg CPU Time",
3:MIN(query_stats.statement_text) AS "Statement Text"
4:FROM
5: (SELECT QS.*,
6:SUBSTRING(ST.text, (QS.statement_start_offset/2) + 1,
7: ((CASE statement_end_offset
8:WHEN -1 THEN DATALENGTH(st.text)
9:ELSE QS.statement_end_offset END
10: - QS.statement_start_offset)/2) + 1) AS statement_text
11:FROM sys.dm_exec_query_stats AS QS
12:CROSS APPLY sys.dm_exec_sql_text(QS.sql_handle) as ST) as query_stats
13:GROUP BY query_stats.query_hash
14:ORDER BY 2 DESC;
15:GO
I am pumped up about TechEd. Bill Gates's last technical keynote. He has to have a bomb to drop... The launch of Windows\VS\SQL Server 2008. All of the speakers are reaching deep into their bag of tricks and bringing new hardcore sessions. The universal party. The blogger's lounge. Twitter, FriendFeed, MSDN, and Technet are all buzzing with posts.
I found out a couple of days ago that a speaker would not be able to make it due to a family emergency. I offered to present his session on DMV's so I will be presenting these sessions:
Using Dynamic Management Views to Improve Your Development
Dynamic Management Views were added to SQL Server 2005 and have been enhanced in SQL Server 2008. While they provide great functionality and usefulness, it appears they are not widely understood or implemented as yet. This session provides an overview of the Dynamic Management Objects available (both Views and Functions) and describes those considered the most useful. It shows how they can be utilized in monitoring, troubleshooting, and ongoing management of SQL Server systems and in gaining an understanding of SQL Server’s operation. The session also illustrates how they can be used to improve the database development experience and how they can be used in custom reports within SQL Server Management Studio.
Database Platform300BRKJason Massie
Windows Management Instrumentation (WMI) for the Command Line DBA
This is a demo filled session on how a command line DBA can unleash the power of WMI. WMI allows the DBA access to information that was previously hard to get or unavailable like the application event logs, OS perfmon counters, and OS events. This session covers WQL queries from SSIS, accessing OS performance counters from SQL, automated reactions to WMI events, WMI from Windows PowerShell and much more.
Database Platform400BRKJason Massie
Troubleshooting Query Plans Gone Wrong
Have you ever flushed the procedure cache to resolve a performance problem? Have you ever had to use a join or index hint when you should not have to? Have you ever updated statistics to fix a performance problem and wondered why it worked? In this session, we get deep into how the query optimizer decides how to execute a SQL statement. We look at common problems that cause the optimizer to choose the wrong access paths like underestimation. We go over a detailed demo on parameter sniffing, problems with local variables and statistics. We also look at solutions to these problems in several areas including good design at the application, schema, and query levels, proactive maintenance and reactive fixes. This session applies to Microsoft SQL Server 2000 through 2008 but we pay special attention to new features in SQL Server 2008 like plan freezing, new hints, filter indexes, and statistics among others.
I'll be there Monday through Saturday. Hope to see you there!