January 19, 2012 at 7:41 am
GSquared (1/19/2012)
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.
Well..Brad McGeHee wrote a entire book on it and he does not say that at all, quite the contrary, so who should I listen to? Him or you? BTW, how many books have you written on the subject? 😀
"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 19, 2012 at 8:01 am
My 2 cents.
Be very cautious when running profiler on a production server because of the performance impact it can/will have.
The recommended better approach is to run a server side trace.
Like the others I too have seen profiler bring a production server to its knees.
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
January 19, 2012 at 8:02 am
Grant Fritchey (1/18/2012)
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.
Your cheque (check) is in the mail :w00t:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 19, 2012 at 8:08 am
GilaMonster (1/18/2012)
Not baloney. I've crashed a production server by starting profiler (with a very limited trace set). I'm far from the only one.
I have done this :blush:
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 19, 2012 at 8:24 am
I think its funny that we are arguing using profiler in this case. Sure, if I have no performance problems and run profiler it might not cause any issues. However, if you are ALREADY experiencing performance problems, you are pushing it by using the profiler GUI especially when you don't know what is causing the problem!
I believe profiler can have many uses on production servers, but I don't believe it is a good tool to be used to identify performance issues on a server that is experiencing problems. I believe that the tool should be used for things like checking to make sure an application is no longer referencing this server because it should be pointed to another, seeing which stored procs are run when I click on the search button in my .NET site that someone else built, etc. Of course you can do a server-side trace for some of these, but in a solid environment it is just plain easier to use profiler.
Jared
CE - Microsoft
January 19, 2012 at 9:25 am
TravisDBA (1/19/2012)
Well..Brad McGeHee wrote a entire book on it and he does not say that at all, quite the contrary, so who should I listen to? Him or you? BTW, how many books have you written on the subject? 😀
Do you realize how you look making a comment like that? First off, Brad's book is about how to get the most out of Profiler, it doesn't go into the internals or performance of tracing to a file versus the rowset provider. I personally happen to have written a book on performance tuning that was contributed to heavily by Gail as my technical editor, and we don't recommend using profiler on a live system in the book because of the performance impacts it can have. I am currently working on my second book on Extended Events and part of the work included comparing performance of Extended Events to Trace, one specific item was server side trace file versus rowset provider with Profiler. It is really simple to test the impact of the rowset provider with Profiler:
USE AdventureWorks2008R2;
GO
IF OBJECT_ID('ExecuteLotsOfStatements') IS NOT NULL
BEGIN
DROP PROCEDURE dbo.ExecuteLotsOfStatements
END
GO
CREATE PROCEDURE dbo.ExecuteLotsOfStatements
(@ExecutionLoopCount INT = 1000)
AS
DECLARE @Loop INT = 0;
WHILE @Loop < @ExecutionLoopCount
BEGIN
DECLARE @Loop2 INT
SELECT @Loop2 = @Loop
SET @Loop = @Loop+1
END
GO
EXECUTE dbo.ExecuteLotsOfStatements @ExecutionLoopCount = 100000;
Run the SP with the supplied parameter value with no tracing a few times to get your baseline (hint: it runs under 200 milliseconds if you were to look at duration with SP:Completed). Then create a trace in Profiler that only collects the SP:StmtCompleted and SP:Completed events, and run it again a few times to get an average. Then export that trace to a script and create the server side trace and run it again a few times.
I can save you some time here though. With a Profiler trace it takes between 25400-31200 milliseconds to complete because of the impact of the rowset provider. With a server side trace it takes between 1400-2300 milliseconds to complete. With a Extended Events Session writing to the file target, it takes 600-800 milliseconds to complete. Don't trust me, run the test yourself.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 19, 2012 at 10:00 am
And if writing a book carries weight (I'd argue it doesn't, but...) then I do have a book about the Profiler and I also argue for using the server-side trace in that book. It's the Performance Tuning one in my signature.
The update currently under production is completely taking Jonathan's advice and going all extended events even instead of the server-side 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 19, 2012 at 10:38 am
Grant Fritchey (1/19/2012)
And if writing a book carries weight (I'd argue it doesn't, but...)
+1. A really good book isn't necessarily due to the authors own knowledge. In most cases it is more about the technical editing and the editors knowledge of the subject matter.
Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
My Blog | Twitter | MVP Profile
Training | Consulting | Become a SQLskills Insider
Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]
January 19, 2012 at 10:42 am
Jonathan Kehayias (1/19/2012)
Grant Fritchey (1/19/2012)
And if writing a book carries weight (I'd argue it doesn't, but...)+1. A really good book isn't necessarily due to the authors own knowledge. In most cases it is more about the technical editing and the editors knowledge of the subject matter.
I can get behind that statement, especially with Joe Sack as a technical editor. Woof!
----------------------------------------------------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 11:28 am
TravisDBA (1/19/2012)
Well..Brad McGeHee wrote a entire book on it and he does not say that at all, quite the contrary, so who should I listen to? Him or you? BTW, how many books have you written on the subject? 😀
May I play?
I wrote a chapter on this topic for both the "SQL Server 2005: Query Tuning and Optimization" and "SQL Server 2008 Internals" books, so I like to think I know a thing or two about it. And in both books I specifically recommend avoiding Profiler if you care about performance.
And know what else? I would trust Gail's word on pretty much any SQL Server topic. And as for you, this is the first I've ever seen your name at all and things haven't started out very well, so you're currently ranked somewhere around -2 on the 1-10 scale of people I trust.
--
Adam Machanic
whoisactive
January 19, 2012 at 12:51 pm
Adam Machanic (1/19/2012)
TravisDBA (1/19/2012)
Well..Brad McGeHee wrote a entire book on it and he does not say that at all, quite the contrary, so who should I listen to? Him or you? BTW, how many books have you written on the subject? 😀May I play?
I wrote a chapter on this topic for both the "SQL Server 2005: Query Tuning and Optimization" and "SQL Server 2008 Internals" books, so I like to think I know a thing or two about it. And in both books I specifically recommend avoiding Profiler if you care about performance.
And know what else? I would trust Gail's word on pretty much any SQL Server topic. And as for you, this is the first I've ever seen your name at all and things haven't started out very well, so you're currently ranked somewhere around -2 on the 1-10 scale of people I trust.
My post wasn't aimed at getting your trust. I have used SQL Profiler for years on limited cases where I was looking for particular things (I don't monitor the whole kitchen sink) and had no issues as long as the server was not crunched to begin with. When someone comes out and says "never" on using it in production, that is when I disagree. Fact. RIF. If I get flamed on that opinion, then so be it 😀
Excerpt from Page 257 on "Mastering SQL Profiler"
"As long as you keep the trace lightweight, and the server is not over-burdened, then the impact of the Profiler trace will not be noticeable to your users"
"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 19, 2012 at 1:08 pm
RIF? Reading Is Fundamental?
Educate the old guy.
----------------------------------------------------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 1:30 pm
TravisDBA (1/19/2012)
Fact. RIF.
Realistic Imitation Firearm ? 😀
Room Inventory Form? 😀
Ruhe in Frieden? 😀
Routing Information Field? 😀
Rain-Induced Fade? 😀
Rapid Innovation Fund? 😀
😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀
--
Adam Machanic
whoisactive
January 19, 2012 at 1:34 pm
Adam Machanic (1/19/2012)
TravisDBA (1/19/2012)
Fact. RIF.Realistic Imitation Firearm ? 😀
Room Inventory Form? 😀
Ruhe in Frieden? 😀
Routing Information Field? 😀
Rain-Induced Fade? 😀
Rapid Innovation Fund? 😀
😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀 😀
Resistance is Futile?!?! LMAO
Jared
CE - Microsoft
January 19, 2012 at 1:39 pm
SQLKnowItAll (1/19/2012)
Resistance is Futile?!?! LMAO
Running is Fantastic?
Rocking it Futuristic?
Rollicking in Fields?
Running in Fear?
I JUST DON'T KNOW WHAT THE ANSWER IS - SOMEONE HELP ME! 😀
--
Adam Machanic
whoisactive
Viewing 15 posts - 31 through 45 (of 53 total)
You must be logged in to reply to this topic. Login to reply