October 8, 2009 at 5:17 am
On loads of our sql servers the devs are struggling with issues such as -
Query takes 10 minutes to run at best. Suddenly takes 3 hours to run.
We have an overnight task where some nights a step with the same number of rows and criteria can take 1 hour ot 6 hours.
How do you go about tackling this? (I've cleared my whiteboard and I'm starting from scratch because I'm getting nowhere so start as simple as you like!)
Thanks,
Shark
October 8, 2009 at 5:26 am
October 8, 2009 at 5:30 am
Yep agree with that, good articles 😛
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 8, 2009 at 7:34 am
Yep. Gail's articles are excellent starting points.
Basically, you just need to gather data. Identify which procedures are causing the most pain and then start tuning them. Use perfmon & trace events to gather data.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 8, 2009 at 7:47 am
Ok so then once you have tuned it as best you can (all indexes are in, the 100% in execution plan where it should be and so forth) and it still performs badly, you move on to perfmon for processor, memory or disk issues?
I struggle to read the results of that because I get all the data, I summarise it all, but I don't know what it means. internet articles tend to just state "You don't want <x counter> to be too high", but never go as far as stating what constitutes high!!!
October 8, 2009 at 7:51 am
Shark Energy (10/8/2009)
Ok so then once you have tuned it as best you can (all indexes are in, the 100% in execution plan where it should be and so forth) and it still performs badly, you move on to perfmon for processor, memory or disk issues?I struggle to read the results of that because I get all the data, I summarise it all, but I don't know what it means. internet articles tend to just state "You don't want <x counter> to be too high", but never go as far as stating what constitutes high!!!
try this link, it lists the basics but it might help
http://www.sql-server-performance.com/articles/audit/hardware_bottlenecks_p1.aspx
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 8, 2009 at 7:58 am
Shark Energy (10/8/2009)
Ok so then once you have tuned it as best you can (all indexes are in, the 100% in execution plan where it should be and so forth) and it still performs badly, you move on to perfmon for processor, memory or disk issues?I struggle to read the results of that because I get all the data, I summarise it all, but I don't know what it means. internet articles tend to just state "You don't want <x counter> to be too high", but never go as far as stating what constitutes high!!!
Yeah, you can do it that way, but even better is to use Wait States and Queues. There are performance monitor counters for these. They'll quickly identify where things are waiting and what they're waiting on. That's the best way to address bottlenecks.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
October 8, 2009 at 8:00 am
Shark Energy (10/8/2009)
I struggle to read the results of that because I get all the data, I summarise it all, but I don't know what it means. internet articles tend to just state "You don't want <x counter> to be too high", but never go as far as stating what constitutes high!!!
Many don't have hard-and-fast values that are 'too high'. In many cases you need to know what's normal for your server so that you can easily tell when things are abnormal.
This podcast should be required listening for anyone getting started with perfmon, or in fact even for people who think they know how to use perfmon
http://runasradio.com/default.aspx?showNum=81
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
November 5, 2009 at 4:37 pm
More questions on this.....
Would a query that sometimes takes 10 minutes, then other times takes 45 minutes actually need tuning? Surely that is pure server performance? Or is it something more sinister?
November 5, 2009 at 4:47 pm
Shark Energy (11/5/2009)
More questions on this.....Would a query that sometimes takes 10 minutes, then other times takes 45 minutes actually need tuning? Surely that is pure server performance? Or is it something more sinister?
Yes a query that performs like that can use tuning. Could other factors cause your query to perform worse at any given time - yes. When it is performing worse than normal, one needs to gather information from the environment that may be causing it. Maybe another query is blocking, maybe there is a runaway server process, maybe there is no query executing but a user session didn't terminate properly. This is where the profiler, perfmon, and other tools come into play.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
November 5, 2009 at 5:54 pm
How much ram do you have an the server? How much of that can SQL Server use? Is your SQL Server memory configuration set to dynamically manage RAM? Are there any other applications installed on the server?
When I see this kind of issue it is often data caching related. If some other large process has occurred and filled the buffercache your data probably has been flushed out of the cache. The next time you run you will be doing physical reads instead of Logical reads. You may even be OS swapping back to disk.
I have seen full text searches fighting with SQL Server for RAM.
November 5, 2009 at 8:05 pm
Shark Energy (11/5/2009)
Would a query that sometimes takes 10 minutes, then other times takes 45 minutes actually need tuning?
Absolutely. In fact, I'd venture to say that eratic performance is worse than constant bad performance. Possibly locking or parameter sniffing, something like that.
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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply