Database Performance:
Performance is the most important
thing that you have to think about, if your database performance is low then
you will face with a lot of calls, emails, and client (customer) dissatisfaction.
A lot of people when got a problem
with performance and customer start complaint about the performance of the
system they always choose the easiest (and costly) way to handle this
issue by asking about new RAM, CUP, DISK. But does that solve the problem? Of course
not, the problem will appear again.
So you need to consider many
factors to analysis your database server performance, the problem may be occurred
because of many different causes:
·
Server Resources.
·
Database Design.
·
Indexes Design.
·
Query.
·
Lock.
·
Dead Lock.
·
And others.
For each of above you have
tool/tools in SQL Server to handle the issue.
Performance Tuning Tools:
·
Windows Performance Monitor
(perfmon).
·
SQL Server Build-in
Reports.
·
SQL Server Activity
Monitor.
·
SQL Server Profiler.
·
Database Engine Tuning
Advisor (DTA).
·
Dynamic Management Views
(DMVs).
Windows Performance Monitor:
By using performance monitor
(perfmon) you can analyze your processor, memory, desk, and network; for
instance you can get a good indicator that you have a memory problem by using
page life expectancy performance counter but this doesn’t ensure that the
problem is actually form your memory may be there is a table scan that affect
your memory by fetch a lot of pages in RAM so the page life expectancy will be
small.
You can find many of important
performance counter in http://elmozamil.blogspot.com/2012/11/system-performance-counter-for-sql.html
and the meaning of each one and
measurement.
Note: the result form this tool
will give you indicator on which area your problem come from, so you need to
take this result in your consideration and move to analysis by another tool.
For example if there is a long
queue in your processor this indicate that you have processor problem, you don’t
need to install another process but you need to optimize your processor by
making sure you have a proper indexes, lock escalation, and sometimes this
problem occur when you have a full database backup run at that time so all you
need may be to move your database backup task to another time and so on.
SQL Server Build-in Reports:
I always start my analysis with
SQL server build-in reports, those report provide you with a lot of helpful
information that may indicates your cause of the problem.
For instance Disk Usage report
(in SQL server 2008) will give you information about your disk space usage by
data files and log file, and it provides useful information on file growth this
information it really helpful it indicate how your file is frequently grow. If you
find you have log file growth every 1 minute you have to think about that why log
file grow every one minute you may decide to increase log file space or growth
amount option so it will be grow every 12 hour and so on.
To open SQL Server Build-in
Reports right click on your server choose report, or right click on a specific
database and choose reports. I encourage you to check and be familiar all
information in all reports.
SQL Server Activity Monitor:
This tool provide information
about Processes, Resource Waits, Data File I/O, and Recent Expensive Queries by
using this tool you may find that your data file for specific database has a
lot of read write operation, there are many queries that execute in log time.
For example if you find that a
data file has a lot of read write operation you may think to move it to a different
disk so no one compete with it for resource and reduce the chance of dead log
to occur and wait.
This tool give the option to kill
a specific process, if you find for example a process that make long wait and
hold resource you may kill it and let other processes to do their jobs.
To access SQL Server Activity
Monitor right click on the server and choose activity monitor.
SQL Server Profiler:
It works like network package
sniffing (package viewer –wireshark-) tools.
Profiler give the ability to view
the actual query run against database so you can analyze each query to check
which column used in joins, where condition. You will find a lot of good
information by using this tool.
To access Profiler go to tools
choose SQL Profiler.
http://msdn.microsoft.com/en-us/library/ms181091.aspx
Database Engine Tuning Advisor
(DTA):
DTA suggest indexes to be created
and to be dropped. To use DTA first you have to capture trace by using SQL
Server Profiler.
Dynamic Management Views
(DMVs):
DMVs provide information relate
to database, indexes, memory usage, lock, deadlocks, and many other helpful information.
You can find a lot about it on
No comments:
Post a Comment