November 5, 2013 at 5:17 am
HI Experts
I have noticed that the performance of our application is slower than expected, I decided to add file to the tempdb then I have noticed a better performance, but after a while the performance came back slower.
am I missing something concerning this issue.
Thank you very much.
November 5, 2013 at 5:37 am
Have tried running profiler and try to find out "Tempdb" is the cause or you query?
Regards
Durai Nagarajan
November 5, 2013 at 6:52 am
Did you investigate and confirm that the cause of the original slowdown was TempDB contention?
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, 2013 at 8:47 am
It doesn't sound like tempdb was the issue. I'd suggest monitoring your wait statistics (sys.dm_os_wait_stats) and running an extended event session to capture query metrics.
Don't run the Profiler gui against production, ever. You can use the gui to generate a server-side trace. You can also use the gui to browse the output of a server-side trace. But don't run the Profiler gui against production.
"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
November 5, 2013 at 1:01 pm
I didn't investigate the main reason for slowing down, but I have noticed the difference in performance after adding a file to tempdb, I expect that something happened made the tempdb is full or something I don't know
I update the statistics twice a week
How to know from profiler that a certain query is making a performance issue
why shouldn't I run gui profiler on production db
Thanks lot
any idea
November 5, 2013 at 1:59 pm
profiler trace is resource consuming.On busy servers, running profiler with GUI causes more issues than actual performance issue(in my experience:-)).
server side trace is lightweight
I found this article helpful comparing profiler and serverside traces
http://www.sqlservercentral.com/articles/Stairway+Series/72469/
run perfmon (include version store size,generation rate and cleanup rate &memory related counters)along with serverside traces..
memory pressure/longrunning transactions cause tempdb space issues
November 5, 2013 at 2:12 pm
zi (11/5/2013)
I didn't investigate the main reason for slowing down, but I have noticed the difference in performance after adding a file to tempdb, I expect that something happened made the tempdb is full or something I don't knowI update the statistics twice a week
How to know from profiler that a certain query is making a performance issue
why shouldn't I run gui profiler on production db
Thanks lot
any idea
The Profiler GUI consumes information from the buffer in such a way that it can actually cause severe memory issues on the server. Server-side trace works differently with the buffer.
But for really light-weight monitoring, you want to use extended events.
To tell if a query is running slow, you first need to be able to compare it to something. That's why we collect the data over a period of time, so you can compare today to yesterday/last week/last month. Also, if you know that your server is suffering from excessive I/O or CPU due to wait states, you can check which queries are consuming the most I/O or CPU. Same thing goes for waits due to memory, parallelism, etc.
"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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply