February 13, 2010 at 5:52 am
Hi All,
I was just wondering aout the question on tempdb which will somehow help me to track performance:-
1)do we need to create secondary file for tempdb?
2) if adding new files then should it be same as of primary and marked as primary?
3) Shifing tempdb to different SAN drive will imporve the performance or only shifting tempdb log file to new SAN drivewill improve the performance?
4) If I am getting lots of timeout in my application log of webserver and my database objects are uptodate then does it direct me to analyse tempdb structure?
still thinking
:cool::cool::cool::cool::cool::cool:
----------
Ashish
February 13, 2010 at 9:39 am
ashish.kuriyal (2/13/2010)
Hi All,I was just wondering aout the question on tempdb which will somehow help me to track performance:-
1)do we need to create secondary file for tempdb?
2) if adding new files then should it be same as of primary and marked as primary?
3) Shifing tempdb to different SAN drive will imporve the performance or only shifting tempdb log file to new SAN drivewill improve the performance?
4) If I am getting lots of timeout in my application log of webserver and my database objects are uptodate then does it direct me to analyse tempdb structure?
still thinking
:cool::cool::cool::cool::cool::cool:
Re you temp db questions This link may help you http://msdn.microsoft.com/en-us/library/ms175527.aspx
In SQL server 2000, i believe it was recommended that you have one data file per CPU...And i think people still do this in the later versions for tempdb. Although Paul Randal in his blog, from 2005 onwards suggests you can have a ratio of 2 or 4 to 1. This link to Paul's blog maybe helpful to you and probably worth reading in some detail if you beleive the problem is with tempdb:
If your web server is giving timeout errors there could be a whole bunch of reasons for this occurring, doesn't neccessary mean tempdb. You will need to delve in a little deeper and look at your baseline, see where the bottleneck is, IO, CPU, Memory Network,
do you have adequate indexes?
Is the query that is timing out optimal?
Hope this helps
Gethyn Elliswww.gethynellis.com
February 13, 2010 at 10:07 am
I tend to agree with Ellis' advice above. Tempdb changes might help you, but you need to pinpoint where you are having issues with your performance. That means examining queries, and the resource needs as they are run.
February 13, 2010 at 2:03 pm
ok thanks for the reply.....
let me further explain.......there was IO bottleneck which get resolved but the problem is there are some reports which if I run at actual server will complete in less than a sec but same query to generate report if I run at client computer, takes more than 5 min and give them timeout. So what could be other reasons....natwork is not a problem at all. 😎
----------
Ashish
February 13, 2010 at 2:38 pm
If you do a Trace on the report using profiler, check the connection settings.
When SQL caches queries is also uses the "SET" statements. One in particular that caused that same issue for me was the "SET ARITHABORT" command.
Within SSMS this is set by default, but may not be within your web services...
February 13, 2010 at 5:44 pm
You definitely need to run a trace to check. Be sure the same parameters and settings are being used.
What is the report consisting of? Are there rendering intensive things taking place in the report.
February 13, 2010 at 8:15 pm
For tempdb structure, check Kimberly Tripps article in my sig below. She has a few articles that are helpful in that area.
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
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply