July 3, 2008 at 2:01 pm
I'm working with our developers to improve the SQL query performance of our database for an upcoming product.
I've been capturing all the SQL commands thrown at the testing servers and later loading them into a database on a different server. With that I can do queries like.
SELECT
TOP 1000
*
FROM
profilerlogs
ORDER BY DURATION DESC
And look at the poorest performing queries and make recommendations to fix them. At this point we've got our slowest queries running faster than a second, and there's not a lot more that I can see to find by using queries like the one above.
This of course is test data... Our largest tables in our test data have only about 150,000 records, which is small compared to what the software will work with as time goes by (insurance document management), so I'm curious if anybody out there knows a tool or method I can use to start looking for bad query plans (Table Scans, Looping Joins, Implied Cursors, etc) that I can basically throw a query at. Something like
SELECT
*
FROM
profilertrace_with_queryplan
WHERE
queryplan like '%Table Scan%' or queryplan like '%Loop Join%'
I know it won't be exactly like above, but while I've dug into profiler, I've usually used it to look at what's going on, command by command, not to search through looking for an estimated query plan that may only show up if you would do CTRL+L in SSMS.
Any suggestions?
July 3, 2008 at 2:28 pm
If you're using 2005, you can retrieve the xml plans from the plan cache. Use the sys.dm_exec_query_stats and the sys.dm_exec_query_plans DMVs. Or you can get profiler to capture the plans and load them into a table
If you're really clued up on xml, xpath and xquery (which I'm not), you can use xquery on the xml plans to find the operators that you're concerned about.
If you're not so clued up on xml and the like, cast the plan to nvarchar(max) and use LIKE on it.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 3, 2008 at 2:35 pm
I don't have that kind of luck...
Is there a method I can use to have a query recompute the estimated query plan when I'm querying it?
The reason I ask this... is I've got about 30 test databases that our beta customers are using.. I'm profilering them all to trace files and about once a week loading the traces into a database for analysis.
When I look at the options for columsn to capture in SQL 2005 I don't see an option for the XML query plan... it shows up as an event, which to me means I get another record in the database, not a column I can work with to find the original query. Since I'm loading 100,000's of executed queries into my table, having the two in seperate records doesn't look as helpful as I'd like.
July 3, 2008 at 3:01 pm
If you're running the queries you should be able to extract the plans from the cache. You can retrieve the text of the query from the cache also. Dunno it that will help you.
I've got some wueries I use for this, but not here. I'll post tomorrow, if that's OK.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 4, 2008 at 5:43 am
This should get you started
SELECT execution_count, total_worker_time, total_physical_reads, total_logical_reads, total_elapsed_time, creation_time, st.text,
qs.sql_handle, qs.plan_handle, qp.query_plan
FROM sys.dm_exec_query_stats qs INNER JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) st
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 7, 2008 at 7:34 am
I outlined a way to hook stuff out of the cached execution plans over here. It's not magic or anything, but you're going to have a tough time assessing what's really worth pursuing and what isn't. Working from the Profiler data is probably going to give you more focused results.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply