Google Custom Search

Thursday, July 31, 2008

Performance Counter for SQL Server

Few of my friends who wish to do a performance tuning in their servers, they asked me what are the counters that they should check that gave me an idea to prepare this document and publish across.

Disk Related Counters:
Object Name: Physical Disk
Counters:
Current Queue Length:
Sustained high queue for a period of time means that the IO subsystem has a problem.

Average Queue Length: If the Average Queue Length for the disk is high more than 2. Then this indicates a potential IO subsystem problem. This would also make the disk sec/read and disk sec/write high.

Disk Sec/Read: This counter should measure not more than 15ms in normal situation. Continuous values of more than 20ms indicate that the disks have a problem.
Under these conditions, consider the following
Move the files to additional drives.
Check for proper indexing on the tables.
Check for the CPU and memory counters to find any other potential bottlenecks.

Disk Sec/Writes: This counter should measure not more than 15ms in normal situation. Continuous values of more than 20ms indicate that the disks have a problem.
Under these conditions, consider the following
Move the files to additional drives.
Check for proper indexing on the tables.
Check for the CPU and memory counters to find any other potential bottlenecks.
Placing transactional log files in separate drives to remove additional pressure on the drives

Memory/Cache:
Object Name: Memory:

Counters:
Page Faults/Sec:
Page faults occur when the page that is not requested is not found in the memory. There are 2 types of page faults. Hard page faults and Soft page faults. Hard page faults require a disk access and where as Soft page faults are ones where the page is found elsewhere in the memory. High number of page faults/sec indicates that there is a problem with memory used by SQL Server. Use this counter in correlation with SQL Server:Memory Manager counters to check if there is a memory pressure.

Pages/Sec: This counter is the actual value of the Hard faults and should be used in correlation with Page Faults/Sec and SQL Server memory Manager counters to find the memory pressure.

CPU:
Object Name: Processor

Counters:
%User Time: SQL Server runs in User mode. Privilege mode is used by Operating system to access hardware and memory components. This counter should have a value more than 70%. If it has a value less than that check the %privileged time and %processor time counters to see if there is a problem with the processor.

%Privileged Time: Operating system moves thread to Privileged mode to access services. This counter should value less than 20%.

%Processor Time: This counter tells the percentage of the time that CPU is executing over a period. Ideally it should value less than 70%.

Interrupts/Sec: Interrupts/sec is the average rate, in incidents per second, at which the processor received and serviced hardware interrupts.

System:Object Name: System
Counters:
Processor Queue Length:
Is the number threads that are waiting to be processed by the processor. This counter is calculated by the actual value divided by the number of processors. Ideally value should be less than 2.

SQL Server:
Object Name: Access Methods
Counters:

Forwarded Records/Sec: Number of records fetched through forwarded record pointers. Tables with NO clustered index can. If you start out with a short row, and update the row creating a wider row, the row may no longer fit on the data page. A pointer will be put in its place and the row will be forwarded to another page.
Can be avoided by:
Using default values
Using Char instead of Varchar.

Full Scans/Sec: Either table or Index in fully scanned. This could be caused because of wrong or unhelpful indexes.

Page Splits/Sec: Number of page splits occurring as the result of index pages overflowing. Normally, this is associated with leaf pages of clustered indexes and non-clustered indexes. This can be avoided by having proper fill factor.

Object Name: Memory Manager
Counters:

Memory Grants Pending: Memory is needed to process each user request. If enough memory is not available then the user request waits for memory which cause performance hitch in executing the query.
This can be avoided by the following
Adding more memory to the server
Adding memory to SQL Server
Creating proper Indexes

Object Name: Buffer Manager
Counters:

Buffer Cache Hit Ratio: Percentage of time the pages requested are already in memory. It should be ideally more than 99% and if less than 95% indicates that SQL server has no enough memory and adding more memory would be beneficial.

Checkpoints/Sec: Pages written to the disk during the Checkpoint process freeing up SQL cache. Memory pressure is indicated if this counter is more than 300 Secs.

Lazy Writes/Sec: Pages written to the disk during the LazyWriter process freeing up SQL cache. Memory pressure is indicated if this counter is more than 300 Secs.

Page Life Expectancy: This is one counter that I would love to monitor to check the memory pressure. This would give you the time in seconds the data pages reside in the SQL Server cache. If the value is low indicates the following problems.
SQL Cache is cold(Discussed in Page Faults)
Memory Problems
Missing indexes.
If Checkpoints/Sec, Lazywriter/Sec and Page life expectancy together is less than 300 seconds then it indicates the cause of memory pressure and your server needs more memory.

Object Name: Databases
Counters:

Transactions/Sec: Indicates the number of transaction that occur per second in the server.

Object Name: General StatisticsCounters:
User Connections: Number of connections made to the SLQ Server.

Object Name: Latches
Counters:
Average Latch wait Time:
Latches are short term light weight synchronization object. Latches are not held for the duration of a transaction. Typical latching operations during row transfers to memory, controlling modifications to row offset table, etc.
A high value here indicates potential problems with IO subsystem or Memory.

Object Name: Locks
Counters

Average Wait Time(ms), Lock Wait time(ms) and Lock waits/Sec: All these counters are related to the locks held by the SQL Server. Transaction should be as short as possible and should hold locks only for a short time without blocking other user queries, A high value here would indicate the following.
Memory Pressure
IO Subsystem problem
Improper indexes
Improper SQL structures
Improper placement of files in the disk subsystem
Incorrect usage of SQL Isolation levels

These are the major counters that I would wish to check. Apart from this you can also check the following counters.


Object Name Counter Name
SQL Statistics Compilations/sec
SQL Statistics Recompilations/sec
SQL Statistics Batch Requests/sec
SQL Server: Buffer Manager Readahead pages/sec

Monday, July 28, 2008

SQL Server Security Bulletin

Introduction:

This document analyzes the information given in the Microsoft bulletin regarding the Security patch that needs to be applied in all SQL Servers version 7 and above.

Scope of the Document:

1. Information regarding the Bulletin
2. Are we affected?
3. Deployment Plans and Guidance
4. Other points to ponder

Bulletin Information:

Bulletin URL:
http://www.microsoft.com/technet/security/bulletin/MS08-040.mspx
http://support.microsoft.com/kb/941203#appliesto

Software Affected: SQL Server 7, 2000 and 2005 (32 bit and 64 bit) and (GDR and QFE releases)





The bulletin describes that a security patch has been released for known vulnerabilities in SQL Server and its components. The known vulnerabilities are listed below.

Memory Page Reuse Vulnerability - CVE-2008-0085
Convert Buffer Overrun - CVE-2008-0086
SQL Server Memory Corruption Vulnerability - CVE-2008-0107
SQL Server Buffer Overrun Vulnerability - CVE-2008-0106

Memory Page Reuse Vulnerability:

With this vulnerability, a database user with access can read other user’s data with custom applications as SQL Server does not initialize the memory pages before reallocating memory to users.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Installing this security path creates a new system stored procedure sp_clean_db_free_space that can be used to clear the residual pages in the memory. This procedure cleans up all files in the database.

Mitigation Factor: Only users with database access can exploit this vulnerability.

Any other Workarounds:

Microsoft Suggests to have the configuration option “Common Criteria Compliance” set to 1. But enabling this option would result in performance degradation of the system and application.

Convert Buffer Overrun Vulnerability:

This vulnerability exists in SQL convert function that allows authenticated user to gain elevated control over the system.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Mitigation Factor: Only users with database access can exploit this vulnerability.

Any other Workarounds: None

SQL Server Memory Corruption Vulnerability:

This vulnerability exists in SQL server that allows authenticated user to gain elevated control over the system.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Mitigation Factor: Only users with database access can exploit this vulnerability.

Any other Workarounds: None

SQL Server Buffer Overrun Vulnerability:

This vulnerability exists in SQL Server function that allows authenticated user to gain elevated control over the system.

Microsoft has not received any information from customers or other users stating that their system is exploited using this vulnerability and information regarding this vulnerability was brought up through responsible disclosure.

Mitigation Factor: Users with database access can exploit this vulnerability. With SQL Server 2005, services by default run on non-admin accounts. Also SQL Server 2005 prompts for a domain account for services.

Any other Workarounds: None

Are we affected?

We can use Microsoft Baseline Security Analyzer (MBSA 2.1) to determine the vulnerabilities in the System. MBSA 2.1 detects these vulnerabilities and reports of any other missing security updates too.

Download URL:
http://technet.microsoft.com/en-us/security/cc184923.aspx

Deployment Plan and Known Issues:

Deployment Instructions are available in
http://www.microsoft.com/technet/security/bulletin/MS08-040.mspx.

Download links and impact instructions for SQL Server













Verification after Installation: Executing the query “Select @@version” should result with the version number given below.








Also the information can be found in the log file on the directory listed below. SQL Server 2000 - %WINDIR%\SQLHotfix SQL Server 2005 - %PROGRAMFILES%\Microsoft SQL Server\90\Setup Bootstrap\LOG\Hotfix

Known Issues:

There are few known issues with this update as listed below.

1. Dependent services remain in stopped state even though SQL Services are started. We have to check and verify that all dependent services are started.
2. SQL Server services or components that are in disabled state will be updated. This security patch update requires that all services to be enabled and running so that the installer detects them and applies the necessary patches.
3. Upon unsuccessful completion, we need to reboot the system so that the changes are rolled back.

953743 (http://support.microsoft.com/kb/953743/) Supported method for applying updates to SQL Server 7.0
953742 (http://support.microsoft.com/kb/953742/) SQL Server 2000 and MSDE 2000 installers stop dependent services
953741 (http://support.microsoft.com/kb/953741/) SQL Server 2005 installers stop dependent services
953740 (http://support.microsoft.com/kb/953740/) How to identify and enable disabled SQL Server instances in SQL Server 2000
953739 (http://support.microsoft.com/kb/953739/) SQL Server 2005 installers do not update an instance of the SQL Server service that is in a
disabled state.

Other Points to Ponder:

This patch is included in SP3 of SQL Server 2005 which is scheduled to release in Third Quarter of 2008. By the time earning freeze is over, we should be having SP3 on which we can schedule to install so that this patch too is applied.

http://blogs.technet.com/dataplatforminsider/archive/2008/04/15/sql-server-2005-sp3-coming-soon.aspx
http://blogs.technet.com/beatrice/archive/2008/04/16/sql-2005-sp3-announced.aspx
http://blogs.technet.com/dataplatforminsider/archive/2008/01/25/microsoft-sql-server-2008-roadmap-clarification.aspx

Wednesday, July 23, 2008

Configuring Peer-to-Peer Replication

I wanted to give a step by step illustatration here too for Peer-to-Peer replicatio. But unfortunately I had almost 26 screenshots in the article and all the images were not able to fit to the size limit of 1024MB. So I am giving you the link to my website where the original article resides. Thanks.


http://www.sql-articles.com/index.php?page=articles/P2P_replication_GUI.html

Saturday, July 19, 2008

Moving System Databases - SQL Server 2005

There are lots of articles, Links and sites that describe how to move system databases from the SQL default location to the location described by the DBA but still lot of users find difficult while moving them and land up in trouble. So I thought of wiriting an article for moving the databases so that the users can use the same and enjoy the task.

Tasks for moving system databases:

1.Moving tempdb databases.

a.) Execute the script below.

USE master;
GO
alter database tempdb MODIFY FILE (NAME = tempdev,FILENAME='NEW PATH');
GO
alter database tempdb MODIFY FILE (NAME = templog,FILENAME='NEW PATH');
GO

b.) Restart services.
c.) Confirm path of database files.

2. Moving model and msdb databases.

a.) Execute the script below.

USE master;
GO
alter database msdb MODIFY FILE (NAME = MSDBData,FILENAME='NEW PATH');
go
alter database msdb MODIFY FILE (NAME = MSDBLog,FILENAME='NEW PATH');
go

USE master;
GO
alter database model MODIFY FILE (NAME = modeldev,FILENAME='NEW PATH');
go
alter database model MODIFY FILE (NAME = modellog,FILENAME='NEW PATH');
go

b.) Stop services
c.) Copy the files to the new location
d.) Restart services.
e.) Confirm path of database files.

3.) Moving master database:

a.) Edit the startup parameters to reflect the new path for –d, –l and -e parameters.
b.) Stop the services.
c.) Move the master and resource database files to the new location
d.) Change the sql port to different one than usual to avoid excess connections and create an alias with that port
e.) Start the services using NET START MSSQLSERVER /f /T3608 (*MSSQLSERVER is for default instance, if you have installed named instance then you need to use NET START MSSQL$Instancename /f /T3608)
f.) Execute the script given below from sqlcmd

USE master;
GO
alter database mssqlsystemresource MODIFY FILE (NAME = data,FILENAME='NEW PATH\mssqlsystemresource.mdf');
go
alter database mssqlsystemresource MODIFY FILE (NAME = log,FILENAME='NEW PATH\mssqlsystemresource.ldf');
go
alter database mssqlsystemresource set READ_ONLY;
go

g.) Stop the services
h.) Change the port back to 1433
i.) Remove the alias
j.) Start sql services.
k.) Confirm if the data files and log files reside on desired path.

Wednesday, July 16, 2008

Monday, July 14, 2008

Short Cut Keys for SQL Server 2000 and 2005

I have always googled out for finding any shortcut key to be used in SQL Server that gave me an idea to post this. I have gathered all the SQL Server 2000 and 2005 shortcuts with the help of books online and internet and posted it in our website which can be accessed from the link below.

http://www.sql-articles.com/index.php?page=articles/shortcut_keys.htm

Thursday, July 10, 2008

SQL Server 2008 in August

Microsoft has announced that SQL Server 2008 will be in August price list. This means that SQL Server 2008 RTM will be launched as scheduled in Q3 2008. The blog entry from dataplatforminsider confirms this from Microsoft.

http://blogs.technet.com/dataplatforminsider/archive/2008/07/09/sql-server-2008-available-on-august-price-list.aspx

Wednesday, July 9, 2008

Thank You - My Friends

These are the mails, messages that i got as wishes for my MVP. Thank you friend. With all your prayers and wishes, I would like to grow.

My Ex-Managers and Ex-Leads:

Thiyagarajan Jayaraman: (My Project Director @ Hexaware)

Congratulations Sugesh. Wish I never lost you. Happy to know that you have not stopped because of the setback. Hope you would not let such things happen again.

Wish you all the best. Thank you for remembering to keep me informed
regards
Thiyagu
-------------------------------------------------------------------
Eyalarasan: (My Team Lead @ CITIGROUP)

Hi Sugesh,

Congratz!

Just went through the site (www.sql-articles.com).. great!

You guys are doing great job... My hearty wishes!!

Regards
Eyal.s
***************************************************************************

Colleagues(Ex and Current):

Sriram Srinivasan

Great News!!! Congratulations now you have to further work hard to retain the title.

All the best
-------------------------------------------------------------------

Ajay

Hi Sugesh,

Great buddy... Hearty Congratulations on your SUCCESS... Ofcourse this is just the beginning.... Nice to know that you are one among the valuable professional's of MS. and I am proud to tell others that I know a MVP... I was checking out your site and it's really excellent and informative...
-------------------------------------------------------------------

Saravanan

Hi Sugesh,

My Hearty Congrats to you.

I know that you are working as a SQL SERVER DBA, and did not have much information other than that.

I am very happy to know that you have made into a number of handful people around the world.

All the Best for your future endeavours

Thanks
Saravanan.S
-------------------------------------------------------------------

Anandhi

congrates on this great achievement Sugesh.
Hats off to you and All the very best for your future as well...
-------------------------------------------------------------------

Roopa Jean:

Hi Sugesh,
congrats a billion Billion....
hey wow so happy for the great acheivement....yes you deserve it....
wish you many such acheivements in the fore coming days......
cheers
Roopa Jean
-------------------------------------------------------------------

Shomi Basu (Current DBA in my project @ Hexaware)

Dear Sugesh,

Hope you remember me. Congratulations for your success and hope to see you even higher. Will bug you again and again for all my doubts.

Regards
Shomik
-------------------------------------------------------------------
Naresh C. (Current DBA in my project @ Hexaware)

Congratulations Sugesh.
***************************************************************************
Friends:

Surabhya Rao:

Hey Sugesh!

Congrats man!! Chance illa....engayo poita...am sure lots of hard work must have gone into it! Very happy to know that all your efforts have got appreciated and acknowledged!!
Very happy to hear this news...congrats once again!! Keep it up :)

Regards,
Surabhya.
-------------------------------------------------------------------
Sriram V(SSN):

Sugesh,
Congratulations and I have already bookmarked the sql-articles page. Good luck with ur efforts!!
-------------------------------------------------------------------
Chakravarthy Kailash:

Machi i am so happy that as if i achieved it............ really so happy machi.......... congrats ........ and really you deserve it for all the hard work you have done so far................
-------------------------------------------------------------------
Viswanathan Swaminathan

Hey Sugesh,
Great to hear that you are one of the MVP. Congrats da!!!!
-------------------------------------------------------------------
Balakumar:

Hey Sugesh,

Way to go man...Congratulations and my Best wishes.

Thanks,
Bala
-------------------------------------------------------------------
Narendran Mohan:
It is great to see you among the top contributors around the globe. Very proud of you...All the best and let this be one more stepping stone for you..

With best regards,
Naren
-------------------------------------------------------------------
Dalavai

This is simply great … Keep going buddy…congrats and all the best.
-------------------------------------------------------------------
Swetha

Wow. Many congratulations Sugesh! That is a definitely a great achievement.
-------------------------------------------------------------------
Sasi:

I'm really proud to see this.. Keep going!! All the best Sugesh.
-------------------------------------------------------------------
Odaiyappan

Congrats Sugesh, Keep going.

Gone through those web pages of microsoft and ur sql site it is an excellent work, i am proud that i shared the class room with you at one point of my schooling.
-------------------------------------------------------------------
Samuel Ignatius:
Hi Sugesh,

many congrats, really good to hear that, specially when i know and have seen you that how much you desired to get this certification. keep in touch.

regards
Samuel
-------------------------------------------------------------------
Vinod Chidambaram
Congrats da Sugu....Very very very happy for you da....God will be always with you...Celebrate this occasion and enjoy life.
-------------------------------------------------------------------
Srividhya Seshan

Hi,
Hearty Congratulations!!! I am sure you will hold it forever.Be an MVP everywhere. Wish you the very best always.
Congrats Once again.

Thanks,
Srividhya.
-------------------------------------------------------------------
Rohini Bharath

Hi Sugesh,

Hearty Congratulationns!!!!!!!!My best wishes for your future achievements toooooooooo
-------------------------------------------------------------------
Dinesh

Congrats da mama.
All the very best. I explored the site a bit,looks clean n simple interface.
Good job,convey my wishes to chakra and seth.
-------------------------------------------------------------------
Rekha

Hey, Congratulations on your success! I feel happy to see a fellow student come so far in his professional life. I wish you success in whatever you do on your way to glory!
-------------------------------------------------------------------


Tuesday, July 1, 2008

Awarded Microsoft MVP

I am coming with the happiest news of my professional career. For people who might know me for long this could be great news and for others it would be great to know this news.

I am now proud to say that I am part of few Microsoft Most Valuable Professional’s (MVP's) around the globe. I have been awarded this for my contributions towards SQL server and will be holding this title for a year from now. (I am sure; I will be going for a renewal too, no giving back again).

At this point, I would like to thank a few people who got me to this. The list might be small but these people mean a lot to me towards achieving this.

Krishna Gopalan - My manager at Hexaware. When I joined Hexaware, i was in infant stage of working with SQL Server. He was the one who had trust in me giving variety of tasks involving lot of SQL Server and other Microsoft products helping me to achieve near expertise. I could still hear him saying "Sugesh, I know you are the right person to do this job". That was the level of confidence that he had on me. I can never forget the ISA implementation that I worked on with his guidance. The hard to understand infrastructure application. Without him, i wouldn't have made it right here.

Stephen Seth - One of my best friends, He too works as a SQL Server DBA. i would literally pain him asking N number of questions and making comments out of everything so that we both were in right path of learning something that we were working on. Lot of my articles, scripts and comments has gone for his review before being published.

Vidhya Sagar and Deepak - My collegaues @ CITIGROUP. These are other 2 guys who have been with me together through out this journey. When we started our website (www.sql-articles.com) they both backed me up in designing the website which a person would like to read things about SQL Server. The great news here is they too are MVPs now.

And my parents and sister, I have no words to thank them. They have always stood besides me on whatever i do. Without their constant support and encouragement i would not have really achieved anything out here.

If i would have forgotten to name anyone here, please apologize me. Thanks for all others supporting me and would love to say
a BIG thanks again.

https://mvp.support.microsoft.com/profile=5888001B-22BC-4DFB-BEF1-F640063A9568