Is SQL Server slow, are queries causing your applications to
be performing less than they should? There are things that you can do to
improve things and make your servers fly.
SQL Version - Consider upgrading to the latest version of
SQL Server – The newer the version, the better the performance, later versions
have better diagnostics, improved SQL Server query optimizer. If you are
running the 32 bit version consider changing to the 64 bit version as this will
allow your SQL Server to use more memory.
Check the size of TempDB – if this database is heavily used
and it is too small to start with the Autogrow default setting of 10% until the
disk is full may be too small, consider increasing this setting to cope with
busy servers. Moving the TempDB to another fast drive can help improve
performance, make sure it is not the default C: drive.
Memory - Physical Server Memory is another point to consider
– SQL 2008 R2 Standard can use a maximum of 64Gb of RAM, so if you have less
than this in your server, upgrade to 64Gb and see your SQL Server fly.
Windows Task Manager – Sort by CPU and then memory. If you
see any processes that you don’t recognise, kill them off. Avoid developers
using RDP to the Server Desktop; restrict this function to only users that need
to access the server. Strip out any software from the Windows Server that is
not required. Configure Anti-Virus software so that it does not interfere with
SQL folders.
On Windows Server 2008 or above, make sure that the windows
file system cache is not chewing up all your memory. Try and avoid file copies
form the server as this may affect performance of the SQL Server due to caching
of the file operation. Check the Task Manager | Performance tab and check the
Cached number under ‘Physical Memory’
Event Logs – Use both the Windows Event log and the SQL
Server log and check for any errors or warnings, fixing these may have a major
impact on the server performance. The Windows logs will show any alerts
regarding potential hardware failure, take note of these and replace hardware
before it’s too late. Drivers for hardware are another area that can cause
problems or slowness; make sure that these are up to date. Configure SQL Server
alerts so you know when storage is tight of there are errors occurring on your
SQL Server.
Run Brent Ozar’s SP_Blitz
script A Health check of your SQL Server
Power Plans – Check that the Windows Server Power Plan is set
to Performance setting and not power saving. Use ‘powercfg /list’ at the CMD
Prompt to see what power plan your server is using.
More information http://www.mssqltips.com/sqlservertip/2225/identify-sql-servers-with-inefficient-power-plans-using-policy-based-management/
SQL Server Simple or Forced Parameterisation. Which should
you be using? It will depend on what type of queries you are using but the
following article will show how to decide on which route to take.
Performance Tuning Tips from mssqltips
Glen Berry’s SQL Server Performance
Depending on your anticipated workload and which SQL Server
components you have installed, there are a number of instance level settings
that you should consider changing from their default values. These include Max
Server Memory, optimize for ad-hoc workloads, default backup compression,
max degree of parallelism, and the number of TempDB data files for the SQL
Server instance.
Useful links
No comments:
Post a Comment