Breaking: Quest-SolidQ Partnership

The podcast from Brian Moran and Bill Bosworth provides good insight into the the deal. Click here.

Here is an excerpt for the forthcoming press release:

Quest Software is announcing its new partnership with Solid Quality Mentors, a global provider of education and solutions for Microsoft data and development platforms.

This is not a sales reseller relationship, but a true joining of forces to provide new real value to the SQL Server community. Specifically, our plans are to work together in three specific areas: community outreach, product development and professional services.

Community outreach: The companies will work together on joint programs including speaking at Quest and Solid Quality Mentors events, authoring technical briefs and white papers and providing joint webcasts and podcasts for the SQL Server community.

Product development: Quest will collaborate with Solid Quality Mentors on product road maps and feature sets, drawing on their combined experience of real-world SQL Server challenges to provide the best solutions possible for SQL Server DBAs and developers.

Professional services: Quest customers will benefit from consulting services offered by Solid Quality Mentors to help implement tools and manage complex environments.

Peep http://quest.com Monday for more details.

email it! |  |   |   |   |   | 

Permalink     1 Comments

NTSSUG June Presentation

Here are the slides and scripts from my June presentation at the North Texas SQL Server Users Group. The presentation was on the Data Collector in SQL Server 2008 but it was short so I also touched on filtered indexes, query hashes and troubleshooting. Enjoy and let me know if you have any questions.

Also Peter Debetta is presenting TONIGHT at 6:30 on the following topics:

"We'll be discussing the various ways you can secure your SQL Server data via encryption, including the new Transparent Data Encryption feature of SQL Server 2008."

 Stop by.

email it! |  |   |   |   |   | 

Permalink     0 Comments

CXPACKET, MAXDOP and your OLTP system

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 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)  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.session_id

, r.reads

, r.writes

, r.row_count

, s.[host_name]

, s.program_name

, s.login_name

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

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

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 SSMS
3.    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);

email it! |  |   |   |   |   |