April 30, 2008 at 6:00 am
For an overall performance tuning (if your RAM and CPU ratings are OK), run a trace on the server and save the output in a file. Then use this file as the load file for the database tuning wizard, available under the tools menu. The wizard will suggest, indexes and statistics and the percentage by which it can improve the current performance. Anything above 30-40% improvement will tell you of an existing improper index plan. Consult with the stake holders and implement. If the improvement %-age is below 10%, there is no problem with indexes and statistics.
On the part of the application, check the activity, if there are too many connections from single hosts, there is a issue in the application code.
Chandrachurh Ghosh
DBA – MS SQL Server
Ericsson India Global Services Limited
Quality is not an act, it is a habit.
April 30, 2008 at 8:54 am
While you're at it... check to see what other traces you have running. Too many or the wrong kind of trace will also slow the world to a crawl.
--Jeff Moden
May 3, 2008 at 5:38 am
Does runnig Profiler and DTA affect performance? Besides, what's the best duration time to run profiler for creating a trace file (e.g. 15mins, 1hr, etc.)
On the part of the application, check the activity, if there are too many connections from single hosts, there is a issue in the application code.
?
Could you tell me how I can check from SQL server side the open connections?
while you're at it... check to see what other traces you have running. Too many or the wrong kind of trace will also slow the world to a crawl.
Do you mean the agent jobs?
May 3, 2008 at 7:37 am
Interesting...Tell the department you are a DBA and needs administration rights on the servers....I had same issue they kept taking it away...I told them i have to read every log on the server error log, system logs.
Totally agree on the SP but get the latest one that came out cu6 we just implemented this and it works great.
With the tempdb thats interetsing, i need to measure the growth size currently then will determine what to set the max size to.
Did you go buy the number of processors and create a files for each i.e if have 4 processors do you have 4 files for tempdb ..just curious.
May 3, 2008 at 8:48 am
Peace - have your company hire a mentor for you for a short period of time. He/she can not only help improve performance in the short term but also enable you to do a better job as DBA in the long run!!
Jeff - NICE AVATAR!!! :hehe:
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 3, 2008 at 10:25 pm
TRACEY (5/3/2008)
Interesting...Tell the department you are a DBA and needs administration rights on the servers....I had same issue they kept taking it away...I told them i have to read every log on the server error log, system logs.
They've told me they will (but when I don't know;))
Totally agree on the SP but get the latest one that came out cu6 we just implemented this and it works great.
sorry I didn't get you. what do you mean by cu6?
Did you go buy the number of processors and create a files for each i.e if have 4 processors do you have 4 files for tempdb ..just curious.
So sorry. it seems this stomachache has stolen my IQ:hehe: do you mean I should create files for tempdb by the number of stored procedures I have?
May 3, 2008 at 10:29 pm
TheSQLGuru (5/3/2008)
Peace - have your company hire a mentor for you for a short period of time. He/she can not only help improve performance in the short term but also enable you to do a better job as DBA in the long run!!Jeff - NICE AVATAR!!! :hehe:
I wish they had:ermm: Anyway, I know that I should improve my knowledge as soon as possible; do you have any suggestion? I'm studying books but they don't give me experience. Actually, the most I learn is from comments of such people like you in forums:cool:
May 4, 2008 at 6:28 am
The latest cumulative 6 service pack for SQL 2005.
May 4, 2008 at 6:29 am
CU7 was released just a few weeks ago.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
May 4, 2008 at 8:56 am
peace2007 (5/3/2008)
Did you go buy the number of processors and create a files for each i.e if have 4 processors do you have 4 files for tempdb ..just curious.
So sorry. it seems this stomachache has stolen my IQ:hehe: do you mean I should create files for tempdb by the number of stored procedures I have?
The standard suggestion for TempDB is to create 1 TempDB file for each processor in the server. If you have 4 processors, then they suggest that TempDB be made up of 4 files on 4 separate drives and 1 of those drives isn't where the main database lives. So, you'd need 5 drives to follow that suggestion properly.
--Jeff Moden
May 4, 2008 at 10:14 am
Jeff Moden (5/4/2008)
peace2007 (5/3/2008)
Did you go buy the number of processors and create a files for each i.e if have 4 processors do you have 4 files for tempdb ..just curious.
So sorry. it seems this stomachache has stolen my IQ:hehe: do you mean I should create files for tempdb by the number of stored procedures I have?
The standard suggestion for TempDB is to create 1 TempDB file for each processor in the server. If you have 4 processors, then they suggest that TempDB be made up of 4 files on 4 separate drives and 1 of those drives isn't where the main database lives. So, you'd need 5 drives to follow that suggestion properly.
Jeff - can you provide a link to where it is suggested that you have a separate drive for each tempdb file? I was not aware of that condition and, in fact, think it is wrong.
See: http://blogs.msdn.com/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx for further information.
Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
May 4, 2008 at 3:24 pm
There's a whitepaper on best practices for TempDB. I'll see if I can find it, I think I have the link at the office,
Regarding the separate drives, it depends on why you're splitting TempDB. Jeff's suggestion is the best possible option for optimal performance of tempDB under any and all conditions
If you're splitting because of IO bottlenecks, then you need separate drives. (separate physical drives, not partitions on the same drive)
If you're splitting because of blocking on the allocation units (typically seen as page latch waits on 2:1:3) then you just need separate files. In this case, from personal experience, you may be able to get away with a ratio of files to processors (my production system has 12 processors and 6 tempDB files), but check to see if the waits go away.
Either way, because of the way the allocation algorithms work, all the files should be the same size and should not have autogrow on.
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
May 4, 2008 at 9:19 pm
jeff.williams3188 (5/4/2008)
Jeff - can you provide a link to where it is suggested that you have a separate drive for each tempdb file? I was not aware of that condition and, in fact, think it is wrong.
I don't see anything in the the URL you posted to contradict what I said. That, not with standing, I'll have to see if I can find the MS article that recommends what I said 'cause you're right... gotta have backup for everything said.
--Jeff Moden
May 5, 2008 at 5:57 am
Here's something:
Working with tempdb in SQL Server 2005
An excerpt...
tempdb supports only one data filegroup and one log filegroup. By default, the number of files is set to 1. Multiple files can be created for each filegroup. Adding more data files may help to solve potential performance problems that are due to I/O operations. Increasing the number of files helps to avoid a latch contention on allocation pages (manifested as a UP-latch). The recommended solution is to set the number of files to match the number of CPUs that are configured for the instance. This is only a recommendation—the number of files might not be the same as the number of CPUs.
Having too many files increases the cost of file switching, requires more IAM pages, and increases the manageability overhead. The size of each file is configurable. More files can be added, moved, or removed. To better use the allocation mechanism (proportional fill), the size of the files should be equal. Generally, for SQL Server 2005 it is recommended that you create tempdb files striped across fast disks.
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
May 5, 2008 at 7:27 am
Thats a good thread - so first monitor and then determine if you need more files.
If you add more files - Do you add first a new FILEGROUP
Or just add files temp1.mdf etc?
Viewing 15 posts - 16 through 30 (of 64 total)
You must be logged in to reply to this topic. Login to reply