MS SQL Server is a separate technology, it is often the case that when problems occur, uncertainty arises regarding the root cause, and problem scope is rarely well outlined. Although many of the details here are not specific to SQL Server problems, they are good practices for troubleshooting many types of complex IT issues. On correct understanding the problem and its cause therefore, accurate root cause diagnosis is very important. Sometimes it is obvious what caused the problem, but not always.
Here we go regardless of the problem, you will need to do some forensic analysis to determine the cause of the problem.
1. Define the problem/ establish a clear problem statement.
2. Determine the problem’s impact/ The IT chef & stake holders often don’t want to know technical details.
3. Engage the correct resources/ both internal or external.
4. Identify potential causes/ meet all necessary parties physically or virtually.
5. Plan and coordinate tasks across teams.
6. Select a communication plan and review/ document the plan and agree who will keep management, end users, and the technical team updated.
7. Identify root cause.
8. Determine solution.
9. Test and implement/ even if the problem does not exist in the test or pre-production environment, implement the fix there first.
SQL Server is typically affected by the following bottlenecks:
🙂 File I/O
🙂 Locking, blocking, or deadlocking
Start with SQL Server Performance Monitor, make sure that the disk counters are on. To do so, run diskperf from a command prompt (cp). If the disk counters are not on, run diskperf -y and then restart the computer. SQL Server Performance Monitor log, collect the following information:
🙂 Paging file
🙂 All SQL Server counters
🙂 Logical disk
🙂 Physical disk
In most cases, you should be able to find enough information using these tools to provide you with enough clues to determine the cause of a particular problem. A good DBA troubleshooting approach, adopting a positive attitude with moderate determination and persistence to identify the root cause and resolve issues sure helps.
A positive attitude leads to better quality results, faster resolution, and it will reduce the stress level for you and co-workers during the troubleshooting process.
SQL DBA behaviors and attitudes are characteristic of the most effective database professionals when troubleshooting complex problems. Remain calm, problems are never random — Problems with computers happen for a reason. Avoid prejudice OR looking for fixes, think ahead, it’s common to see spend a lot of time troubleshooting numerous issues that have nothing to do with the main source of the problem. Determine the problem’s real impact on the company business.
🙂 How severely is the system affected?
🙂 How many users cannot work?
🙂 Is money being lost? quantify the amount.
🙂 What is the visibility of the issue?
🙂 Are external customers affected?
🙂 Could any compliance obligations be breeched?
🙂 How serious are the consequences if the problem persists?
IT and business agreements a service-level agreement (SLA), recovery point objective (RPO) and recovery time objective (RTO). Environment overview (network diagram, application architecture), Windows System and Application Event Logs and SQL Server Error Logs, give time to understand the problem and application environment often leads to a higher quality and faster problem resolution. Gather all relevant logs to a single location:
🙂 Windows and System Event logs
🙂 SQL Server Error Logs
🙂 Dump fi les
🙂 Application logs
Many problems begin life as an application behavior or performance issue, and there may be other software components or interactions that could affect the database platform. Simply DBA bring in and nothing to with data or database, anyway what to look for when troubleshooting each major problem.
🙂 Connectivity issues -Netowrk (NIC)
🙂 Performance issues — For a performance problem you need to determine if the problem is on the client, the middle tier, the server on which SQL Server runs, or the network.
🙂 Hardware bottlenecks — Identify resource contention around disk(Review the PerfMon disk counters for Average Disk Sec/Read and Average Disk Sec/Write to verify that the time to make a read or write is ideally below 20 to 30 milliseconds for OLTP systems, higher for decision support systems), CPU (maximum degree of parallelism), network, or memory(AWE feature was discontinued in SQL Server 2012).
🙂 SQL Server issues — As well as hardware contention, SQL Server has fi nite internal resources, such as locks, latches, worker threads, and shared resources such as tempdb.
🙂 Compilation issues — If possible, identify one user query that is slow, the most common causes are insufficient resources. This could be caused by a sub-optimal query plan as a result of missing or outdated statistics, or ineffi cient indexes. SQL Server performance issues with simple tools Data Collector Set, performance trace using the most basic tool:
* Click on Start | Run and type Perfmon.exe
* Go to “Data Collector sets”, expand it and then right click on “User Defined”.
* Choose to create a new “Data Collector Set”.
* Give a name to the set, e.g. “SQLPerf”. Also choose to “Create Manually (Advanced)”. Click on Next.
* Then choose to “create data logs” and check only the “Performance counter” option. Click on Next.
DBA like to add all the counters and all instances from the below Perfmon objects, so that we can capture all the details. Operating System Objects:
* Network Interface
* Paging File
* Physical Disk
* Server Work Queues
SQL Server Objects:
* SQLServer: Access Methods
* SQLServer: Buffer manager
* SQLServer: Locks
* SQLServer: SQL Statistics
I usually set the interval of the Perfmon Log to 10 to 20 seconds. After you stop the trace, it should be saved have a file extension of .blg. After all this hardwork what comes out, the latter case usually indicates that the performance problem is somewhere outside of SQL Server. Other think, dba want to eascape from problemo and complain other dude’s (:
Let me know if you have any further question and your comments will be learning point.
Microsoft Certified Solutions Associate (MCSA)
Tagged: SQL Server