Scripts

Question

Post reply

Scripts

Joy Smith San

SSC-Insane

Points: 24877
More actions
April 27, 2011 at 1:34 am

#389389

Hi All,
One of our client gave us a database of size 300+ GB and with many users (SQL Server 2005 Standard edition).
They asked us to give "suggestions" for better perfomrance and mainteneance.
What are the things do we need to check (in brief...).?
And can someone give me the required scripts, if allready available.
Thanks.

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

Patrick_Fiedler SSCommitted Points: 1874 More actions · Answer 1

Hi,

please post details of the server. What server is it? Is it virtualized? What about the memory and storage?

What other databases are running on this server?

But you can check whether to add memory to the server with following script:

-- Buffer cache hit ratio

SELECT (a.cntr_value * 1.0 / b.cntr_value) * 100.0 [BufferCacheHitRatio]

FROM (SELECT *, 1 x FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio') a

CROSS JOIN

(SELECT *, 1 x FROM sys.dm_os_performance_counters

WHERE counter_name = 'Buffer cache hit ratio base') b

-- Page life expectancy

selectcntr_value as [PageLifeExpectancyInSeconds]

fromsys.dm_os_performance_counters

whereobject_name like '%:Buffer Manager%'

and counter_name like 'Page life expectancy%'

[font="Arial"]Kind regards,

Patrick Fiedler
Consultant

Fiedler SQL Consulting

www.fiedler-sql-consulting.de
[/font]

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 2

Thank You.

SQl server 2003 64bit, SP2

16 GB RAM

720+ GB Hard Disk

Intel[R] Xeon [TM] MP CPU

Dedicated fot this particular database.

Not virtualised.

Thanks. again

Patrick_Fiedler SSCommitted Points: 1874 More actions · Answer 3

What storage your sql server is connected with? SAN?

Have you distributed the OS, the data and log files over separate disks?

What are the results of the script?

[font="Arial"]Kind regards,

Patrick Fiedler
Consultant

Fiedler SQL Consulting

www.fiedler-sql-consulting.de
[/font]

Joy Smith San SSC-Insane Points: 24877 More actions · Answer 4

No it's not connected to SAN..

Log files are in the same disk but in different drive.

Thanks.

Grant Fritchey SSC Guru Points: 397721 More actions · Answer 5

Just saying you have a 300gb database with many users is not nearly enough to begin to make suggestions for what to do with that database. It's not even remotely near enough information. Is it an OLTP system or a reporting system? That right there changes recommendations. Does it have referential integrity, how are the clustered indexes laid out, what kinds of queries are run against it, do you have a set of performance monitor baselines, do you have a trace showing which queries are called and how they behave.... There's just so much that has to be outlined. I'll do a review of a system, but it's a four hour process and I charge for it.

For a minimum of what to get started with, do a search on google or bing for Brent Ozar's blitz script. It's not everything, but it'll get you going.

----------------------------------------------------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

Patrick_Fiedler SSCommitted Points: 1874 More actions · Answer 6

And the disk drive is RAID X??? Or is that a default consumer hard disk?

Have you checked the performance monitor to track "Avg read/sec", "Avg write/sec", "Avg sec/read", "Avg sec/write"? How many transactions per second are executing? How many users are working with the database?

On the first look I think the bottleneck is the hard disk like in most cases.

If you have the opportunity to add a separate disk for all log files and perhaps (depends on usage of tempdb) move tempdb to another disk.

But what about the output of the script????

But nevertheless I also agree with Grant. 😎

[font="Arial"]Kind regards,

Patrick Fiedler
Consultant

Fiedler SQL Consulting

www.fiedler-sql-consulting.de
[/font]

SQL_Helper SSC Eights! Points: 837 More actions · Answer 7

Hi;

Thanks for the query to check "BufferCacheHitRatio" & "PageLifeExpectancyInSeconds". below is the result of my one SQL 2005 server, can u please advice action to enhance server performance as per the below information.

BufferCacheHitRatio

99.0439770554493

&

PageLifeExpectancyInSeconds

44713

sachnam SSCrazy Points: 2317 More actions · Answer 8

ali.siddiqui (5/3/2011)
Hi;
Thanks for the query to check "BufferCacheHitRatio" & "PageLifeExpectancyInSeconds". below is the result of my one SQL 2005 server, can u please advice action to enhance server performance as per the below information.
BufferCacheHitRatio
99.0439770554493
&
PageLifeExpectancyInSeconds
44713

These are just the basic counters to understand if there is memory pressure in your current Enviornment. But the values which you shared looks quite good. So, on the first place i don't think you have any memory related issue with your server. But it is just a begining.. Nobody could anaylse the exact database performance based on these 2 counters.

So, As grant suggested you should Look for Brent Ozar scripts , he is realy a wonderful guy with lot many information about baselining for database performance.

Regards,

Sachin

SQL_Helper SSC Eights! Points: 837 More actions · Answer 9

Thanks for the replay.

I just want to know that If I run in our other SQL servers, what will be the counter values required my attention.

I mean counter value range require to think for RAM upgradation or alarming condition to further investigation.

Thanks again for quick response...

sachnam SSCrazy Points: 2317 More actions · Answer 10

SQL_Helper (5/4/2011)
Thanks for the replay.
I just want to know that If I run in our other SQL servers, what will be the counter values required my attention.
I mean counter value range require to think for RAM upgradation or alarming condition to further investigation.
Thanks again for quick response...

Ideally, the buffer cache hit ratio should be as close to 100 % as possible. but a value anywhere near 99% is considered realy good. Having said that, i would like to tell you that you would found this value always close to 99% in most of your enviorments even if there may be some memory pressure.So, for me it is not the ideal counter to track memory related issues..

For Buffer manager:Pagelifeexpentancy again it depends but ideally a page must remain in the buffer pool for at least 5 minutes which means a value more than 300 could be considered good.

But again as i said it depends, i don't want to put anything which might mislead you or urge you to make a decision. To ensure that there is some H/W bottleneck in your Enviornment better you first check out this link and go further in terms of mesuring performance and exploring more such perfom counter which could indicate something measurable.

http://qa.sqlservercentral.com/articles/Administration/performancemonitoringbasiccounters/1348/

Regards,

Sachin

homebrew01 SSC Guru Points: 55454 More actions · Answer 11

Some environments allow users and developers to run queries against production, or have development on the same server, or allow other application and processes to run on the production SQL server.

So, performance problems sometimes are not caused directly by production sql processes, but by allowing other processes to interfere.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

Few additions to checklist

1. Check TempDB

2. Maintenance Plans

3. select some heavy resource consuming queries.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)