February 16, 2011 at 9:05 am
For performance monitoring of sql server, and troubleshoot what query caused longer transaction, I guess one of the tools is used mostly is performance monitor.
But in performance monitor, after I add counters, and start tracing, those numbers don't make much sense to me.
Is there a good article that helps DBA of sql server to troubleshoot performance issues using performance monitor, and make really sense about what those numbers mean and how to resolve them?
Thanks.
February 16, 2011 at 9:15 am
There are whole books on the subject, plus huge portions of websites like MSDN.
Do you prefer books or online research, or some mix of the two?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 16, 2011 at 9:51 am
Thank you, I prefer online articles.
But if there is a book, i'm interested too.
Thank you
February 16, 2011 at 9:59 am
If you prefer online, I'd just search SQL Server Performance Monitor in your search engine of choice, and start digging in.
There are several articles on this site. Check out the Articles sections, look for Performance Tuning.
You'll find a ton of others elsewhere as well.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
February 16, 2011 at 11:48 am
Performance monitor is absolutely one of the tools you want to know how to operate to understand what's happening on the server, but if you're trying to determine why a particular procedure is running long, you should be checking out the executoin plans for that query. If you're not sure which query is causing the slow down on the system you might be better with a server side trace. Like Gus says, there are multiple articles here on SSC and over on Simple-Talk.com that can help you with this.
"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
February 16, 2011 at 11:57 am
Thank you, when you say run a server trace, is that using SQL profiler?
One time when one of our servers got a hang or ran very slow, I tried to run SQL profiler, I remember it is so slow even cannot be run.
What to do in this situation?
Thank you!
February 16, 2011 at 12:12 pm
Anna_SQL (2/16/2011)
Thank you, when you say run a server trace, is that using SQL profiler?One time when one of our servers got a hang or ran very slow, I tried to run SQL profiler, I remember it is so slow even cannot be run.
What to do in this situation?
Thank you!
Profiler can be used to create a trace (here's an article on that[/url]), but no, you should not run Profiler on your production system. Instead, a server-side trace, which generally, depending on what events and columns you put into it, consumes less than 1% of resources on all the systems I've tried it out on.
"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 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply