January 18, 2012 at 11:01 am
GSquared (1/18/2012)
TravisDBA (1/18/2012)
Run a SQL Profiler for about a half hour to hour on just that database id, and sort by duration descending and save the trace file (.trc). That will give you a real good clue on the queries/stored procedures that are taking the most time to execute, then pull the execution plans on those queries. Of course, I am assuming here you know how to read an execution plan. If you are a db consultant, you should. Then I would run the Index Tuning wizard on that database using the .trc file above as well and see what recommendations it points out. That's just for starters. 😀The problem with that approach is that you really need to account for "fast but frequent" queries as well as long-running queries.
If you have a proc that runs in 10 seconds (very long on most OLTP systems), but only gets run once per day, that's a lower priority target than the proc that runs in 10 milliseconds, should run in 1 millisecond, and gets run 200 times per second. Sorting by descending duration will give you the 10-second proc. So also make sure to aggregate total run-time, not just look at per-incident runtime.
Also, you can have hidden performance eating code that you won't catch in a simple trace. For example, you might not notice that all your insert/update/delete operations are taking longer than they should, because of a trigger on a heavily-hit table. A trace won't usually catch that, but something like Confio Ignite, SQL Foglight, et al, will. That's why I would start with the free verion of one of those, instead of relying on a trace. They'll also catch common UDFs (another form of hidden code), that are eating up wait-state-time across multiple procs.
Which is why I stated very clearly to run the trace up to a hour at a time. Doing that also tracks the number of calls to a certain problematic procedure, as well as its duration each time it is called. If at the end of your trace after an hour, you had one procedure calll that was ten seconds, but another procedure that was called 20 times that was averaging 7 seconds, then of course you would see that in the aggregated trace file and act accordingly on the procedure call that was 7 seconds first. Using common sense when reading a SQL Profiler trace is something I should not have just assumed everyone knew. Also, noticed that I also stated that these things you can start with. This does not preclude using other tools to find other hidden issues like trigger calls. I still say that using SQL Profiler is a good place to start, and based on what you find, or don't find there, then you can work your way to other tools to dig deeper. RIF 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
January 18, 2012 at 12:48 pm
I still say that using SQL Profiler is a good place to start
It hasnt been said explicity in this thread , so i will 🙂
Never run profiler live. Server side tracing / extended events are what you need.
January 18, 2012 at 2:30 pm
Dave Ballantyne (1/18/2012)
I still say that using SQL Profiler is a good place to start
It hasnt been said explicity in this thread , so i will 🙂
Never run profiler live. Server side tracing / extended events are what you need.
Baloney! I have run it for years with no issues. The secret is to run it for only short periods of time and to filter only the events you want to see and only the database you are examining(not all databases), and not all day long, or continously on the entire server. Then it is a big performance hit. The below excerpt is right from SQL BOL:
"Use SQL Server Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process, and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time."
Beware of making blanket statements, particularly in SQL Server. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
January 18, 2012 at 2:37 pm
Grant has done a good right up of the reasons why, http://www.scarydba.com/2008/12/18/profiler-research/
Given that there are easy and viable alternatives, I wouldnt want to take the risk with my production servers.
January 18, 2012 at 2:38 pm
TravisDBA (1/18/2012)
Dave Ballantyne (1/18/2012)
I still say that using SQL Profiler is a good place to start
It hasnt been said explicity in this thread , so i will 🙂
Never run profiler live. Server side tracing / extended events are what you need.
Baloney! I have run it for years with no issues.
Not baloney. I've crashed a production server by starting profiler (with a very limited trace set). I'm far from the only one.
The GUI takes latches that server-side trace doesn't. The fact that you haven't personally had problems does not mean that the Profiler Gui does not cause problems run against busy production servers even with a limited set of events.
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
http://www.sqlmag.com/article/performance/sql-server-profiler-or-server-side-trace-
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
January 18, 2012 at 2:41 pm
TravisDBA (1/18/2012)
Dave Ballantyne (1/18/2012)
I still say that using SQL Profiler is a good place to start
It hasnt been said explicity in this thread , so i will 🙂
Never run profiler live. Server side tracing / extended events are what you need.
Baloney! I have run it for years with no issues. The secret is to run it for only short periods of time and to filter only the events you want to see and only the database you are examining(not all databases), and not all day long, or continously on the entire server. Then it is a big performance hit. The below excerpt is right from SQL BOL:
"Use SQL Server Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process, and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time."
Beware of making blanket statements, particularly in SQL Server. 😀
Grant is an expert on execution plans and I respect him very much. However, one article on SQL Profiler does not make him an expert on SQL Profiler. That is his opinion and he is more than entitled to it. Like I said, as long as you keep the time down and limit the things it can look at SQL Profiler is a great tool in your arsenal, just use some common sense with it, that's all. I have used it for years on both busy and non-busy servers, but I don't look for everything under the sun. Don't throw in the entire kitchen sink of things and you will be ok. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
January 18, 2012 at 2:47 pm
TravisDBA (1/18/2012)
Don't throw in the entire kitchen sink of things and you will be ok. 😀
More correct to say "Don't throw in the entire kitchen sink of things and you will probably be ok", but then there are the cases where you won't.
You want to use it, your choice, but unless you have done comprehensive tests (like Linchi and Andrew) and show that it has no effect, don't state that it has no effect. That you have not noticed an effect does not mean there isn't one.
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
January 18, 2012 at 2:51 pm
GilaMonster (1/18/2012)
TravisDBA (1/18/2012)
Don't throw in the entire kitchen sink of things and you will be ok. 😀More correct to say "Don't throw in the entire kitchen sink of things and you will probably be ok", but then there are the cases where you won't.
You want to use it, your choice, but unless you have done comprehensive tests (like Linchi and Andrew) and show that it has no effect, don't state that it has no effect. That you have not noticed an effect does not mean there isn't one.
Please don't put words in my mouth that aren't there. I believe I said I had "no issues", I did not say it had "no effect", that is different. All traces have an "effect" but you can minimize it by narrowing your search and on the database you suspect. From Brad McGehee's own book "Mastering SQL Profiler" he states right at the start in the introduction "I have been a SQL Server DBA for 14 years and I regard Profiler as one of the most useful of SQL Server's "built in" tools". Right from Brad MeGehee himself. 😀 RIF.
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
January 18, 2012 at 3:04 pm
TravisDBA (1/18/2012)
TravisDBA (1/18/2012)
Dave Ballantyne (1/18/2012)
I still say that using SQL Profiler is a good place to start
It hasnt been said explicity in this thread , so i will 🙂
Never run profiler live. Server side tracing / extended events are what you need.
Baloney! I have run it for years with no issues. The secret is to run it for only short periods of time and to filter only the events you want to see and only the database you are examining(not all databases), and not all day long, or continously on the entire server. Then it is a big performance hit. The below excerpt is right from SQL BOL:
"Use SQL Server Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process, and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time."
Beware of making blanket statements, particularly in SQL Server. 😀
Grant is an expert on execution plans and I respect him very much. However, one article on SQL Profiler does not make him an expert on SQL Profiler. That is his opinion and he is more than entitled to it. Like I said, as long as you keep the time down and limit the things it can look at SQL Profiler is a great tool in your arsenal, just use some common sense with it, that's all. I have used it for years on both busy and non-busy servers, but I don't look for everything under the sun. Don't throw in the entire kitchen sink of things and you will be ok. 😀
I wouldn't even say I was an expert at execution plans. Paul can run rings around me without breaking a sweat. That's what an expert looks like.
But, on topic, it does have added overhead. Period. Can you run it without issue? Yes. Are you possibly introducing risk that with all the alternatives in front of us is unnecessary? Yes.
So, I wouldn't do it and I advise against it. That's all.
----------------------------------------------------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
January 18, 2012 at 3:07 pm
Grant Fritchey (1/18/2012)
TravisDBA (1/18/2012)
TravisDBA (1/18/2012)
Dave Ballantyne (1/18/2012)
I still say that using SQL Profiler is a good place to start
It hasnt been said explicity in this thread , so i will 🙂
Never run profiler live. Server side tracing / extended events are what you need.
Baloney! I have run it for years with no issues. The secret is to run it for only short periods of time and to filter only the events you want to see and only the database you are examining(not all databases), and not all day long, or continously on the entire server. Then it is a big performance hit. The below excerpt is right from SQL BOL:
"Use SQL Server Profiler to monitor only the events in which you are interested. If traces are becoming too large, you can filter them based on the information you want, so that only a subset of the event data is collected. Monitoring too many events adds overhead to the server and the monitoring process, and can cause the trace file or trace table to grow very large, especially when the monitoring process takes place over a long period of time."
Beware of making blanket statements, particularly in SQL Server. 😀
Grant is an expert on execution plans and I respect him very much. However, one article on SQL Profiler does not make him an expert on SQL Profiler. That is his opinion and he is more than entitled to it. Like I said, as long as you keep the time down and limit the things it can look at SQL Profiler is a great tool in your arsenal, just use some common sense with it, that's all. I have used it for years on both busy and non-busy servers, but I don't look for everything under the sun. Don't throw in the entire kitchen sink of things and you will be ok. 😀
I wouldn't even say I was an expert at execution plans. Paul can run rings around me without breaking a sweat. That's what an expert looks like.
But, on topic, it does have added overhead. Period. Can you run it without issue? Yes. Are you possibly introducing risk that with all the alternatives in front of us is unnecessary? Yes.
So, I wouldn't do it and I advise against it. That's all.
Well Brad McGehee wrote a whole book on it and he states right at the front of the introduction that he feels the tool is the most useful productive "built-in" tool in SQL Server. I would take his word on that. Have you read his book, Mastering SQL Server Profiler? Why would he say that if it is such a risk? As you all state, that haven't written a book on the subject?:-D
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
January 18, 2012 at 3:21 pm
The thing is, you can still use Profiler with all the cool stuff it does for you. It can generate TSQL for you to output trace events to a file, which it can then read. From there you can search, filter, combine with perfmon, all the great things you get in Profiler. I'm not saying don't use the tool at all. I'm just saying because of how it works, I wouldn't point the GUI at a production system.
What the Profiler GUI does and how it does it is very clearly defined, so we're not arguing facts. We're arguing opinion. You are absolutely welcome to yours. It's not wrong. I've got mine. It's not wrong either. They differ. You see a minimal risk you're more than willing to take. I see a more than minimal risk I want to avoid. Same risk, different conclusions. No worries.
And yeah, I've read through Brad's book, but not all of it.
I've been spending more & more of my time with extended events. If you can, start there. It's better than Profiler and trace.
----------------------------------------------------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
January 18, 2012 at 3:28 pm
Ok , so Brad says
"Another way to use Profiler is to monitor live,
production activity on a 24/7 basis. Wait just a moment. Did I just recommend using Profiler 24/7?
Isn't this contrary to the advice you've read throughout this book, recommending you do everything you
can to minimize the tracing overhead on your servers?
Well, as with every rule, there are exceptions. Generally speaking, if your production server does not
show any signs of performance bottlenecks, and you configure a lightweight trace with judicious use of
filters to minimize the number of events returned, Profiler can be a very useful real-time monitoring
tool."
My emphasis , though i havent read the rest of the book, just skipped to the best practices.
Personnally, i see no advantage in running profiler over a server side trace , even less over extended events.
I will state, I have improved performance of a slow running system by killing multiple "light weight" profiler sessions. More heavier and heavier traces were created to find the 'slow performing proc' 🙂
As Gail said , your choice , but its not mine 😉
EDIT : Just an aside , just because its in a book doesnt make it right http://sqlblogcasts.com/blogs/sqlandthelike/archive/2011/12/31/book-review-sql-server-secret-diary-know-the-unknown-secrets-of-sql-server.aspx though i wouldnt not for one second say that Brads book could be that wrong 🙂
January 18, 2012 at 3:28 pm
Grant Fritchey (1/18/2012)
The thing is, you can still use Profiler with all the cool stuff it does for you. It can generate TSQL for you to output trace events to a file, which it can then read. From there you can search, filter, combine with perfmon, all the great things you get in Profiler. I'm not saying don't use the tool at all. I'm just saying because of how it works, I wouldn't point the GUI at a production system.What the Profiler GUI does and how it does it is very clearly defined, so we're not arguing facts. We're arguing opinion. You are absolutely welcome to yours. It's not wrong. I've got mine. It's not wrong either. They differ. You see a minimal risk you're more than willing to take. I see a more than minimal risk I want to avoid. Same risk, different conclusions. No worries.
And yeah, I've read through Brad's book, but not all of it.
I've been spending more & more of my time with extended events. If you can, start there. It's better than Profiler and trace.
No problem, and I absolutely agree. I just disagree with "blanket statements" made about that built-in tool, that's all. Never say "never". BTW, loved your book, on Execution Plans. It is a mainstay at my desk. 🙂
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
January 18, 2012 at 3:38 pm
TravisDBA (1/18/2012)
Grant Fritchey (1/18/2012)
The thing is, you can still use Profiler with all the cool stuff it does for you. It can generate TSQL for you to output trace events to a file, which it can then read. From there you can search, filter, combine with perfmon, all the great things you get in Profiler. I'm not saying don't use the tool at all. I'm just saying because of how it works, I wouldn't point the GUI at a production system.What the Profiler GUI does and how it does it is very clearly defined, so we're not arguing facts. We're arguing opinion. You are absolutely welcome to yours. It's not wrong. I've got mine. It's not wrong either. They differ. You see a minimal risk you're more than willing to take. I see a more than minimal risk I want to avoid. Same risk, different conclusions. No worries.
And yeah, I've read through Brad's book, but not all of it.
I've been spending more & more of my time with extended events. If you can, start there. It's better than Profiler and trace.
No problem, and I absolutely agree. I just disagree with "blanket statements" made about that built-in tool, that's all. Never say "never". BTW, loved your book, on Execution Plans. It is a mainstay at my desk. 🙂
Thanks glad to hear it. I rewrote it to fix all the problems in it, but then we figured out a whole bunch of other stuff to add to it, so I'm schedule to rewrite the rewrite after I finish the two books I'm working on currently. Speaking of which, I need to get back to work on that stuff & stop hanging out with you guys.
----------------------------------------------------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
January 19, 2012 at 6:56 am
GilaMonster (1/18/2012)
TravisDBA (1/18/2012)
Dave Ballantyne (1/18/2012)
I still say that using SQL Profiler is a good place to start
It hasnt been said explicity in this thread , so i will 🙂
Never run profiler live. Server side tracing / extended events are what you need.
Baloney! I have run it for years with no issues.
Not baloney. I've crashed a production server by starting profiler (with a very limited trace set). I'm far from the only one.
The GUI takes latches that server-side trace doesn't. The fact that you haven't personally had problems does not mean that the Profiler Gui does not cause problems run against busy production servers even with a limited set of events.
http://sqlblog.com/blogs/linchi_shea/archive/2007/08/01/trace-profiler-test.aspx
http://www.sqlmag.com/article/performance/sql-server-profiler-or-server-side-trace-
Ditto.
Profiler is a pretty limited-use tool. Not because of what it can do for you, but because of what it can do to you.
- 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
Viewing 15 posts - 16 through 30 (of 53 total)
You must be logged in to reply to this topic. Login to reply