June 4, 2013 at 6:45 am
Hi,
am new to sql server dba and recently my client has moved his db's from SQL 2000 to SQL 2008 through Detach and Attach. We have run the update statistics, but we have not found any improvement in the report generation. The reports are getting generated from dynamic queries, and now, am looking how to improve the performance from SQL Server side, not on the code. The code part will come later. Need suggestions on what settings do I need to do on SQL Server 2008.
June 4, 2013 at 7:26 am
Your question is very broad and we have no information about the databases, the queries being run, the OS (32 vs 64 bit) or the server hardware and disk layout so it is not possible to tell you what "settings" to change (if any) to improve performance.
The fact that you are experiencing no change in performance by moving the databases from 2000 -> 2008 is not at all surprising if nothing else has changed other than the SQL Server version. 2008 has many more features and options that can provide for improved performance in many cases but you will need to know what they are and make changes to your code, some database objects or even file layouts to leverage these performance features.
The probability of survival is inversely proportional to the angle of arrival.
June 5, 2013 at 3:49 am
Please find few of the details I could get.
- Microsoft SQL Server 2008 R2 (SP2) - 10.50.4000.0 (X64) Jun 28 2012 08:36:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1).
- Client has added 4.mdf (marked on Primary) & 1.ldf file in TempDB, and provided 10% as growth rate, with unrestricted option.
I know this information is not yet upto the requirement, but based on this can you suggest what further things I can do on the server to make the db use the latest features of SQL 2K8R2.
June 5, 2013 at 6:22 am
I would start by examining the queries executed to generate the report. Since they haven't changed, I wouldn't expect that performance would improve too much. You might have some opportunities to improve the structure of the queries to improve performance, but the only way to tell is by digging into them. Indexing will be a part of the digging, but don't approach this haphazardly. Take care to use a methodical approach; you don't want to impact performance in other areas.
June 5, 2013 at 6:28 am
There at still many unanswered questions such as what sort of disk arrays are configured, and how is tempdb and the application databases deployed on these arrays? What is the overall size and transaction rate(s) of the application database(s)? How much memory is installed in the server and how much allocated to SQL Server?
Good partitioning of data in the databases is one key area that can have a great effect on performance as well as concurrency and of course the quality of the database design itself if critical to performance and scalability. How normalized is the data and what about fragmentation and/or page splits? Check on that .. maybe index rebuilds and statistics updates are called for.
It is good that tempdb has been been reconfigured for several files if the the disk subsystem can handle the addition I/O capability. Still not a lot to go one to provide any more specific advice to you. If you can provide more specifics on the performance problems you are having (if any) we may be able provide more assistance.
The probability of survival is inversely proportional to the angle of arrival.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply