1 / 38

Troubleshooting Techniques

Troubleshooting Techniques. Lesson 29. Skills Matrix. Skill Matrix. Troubleshooting. Develop a systematic approach to solving problems: guessing rarely proves effective. You have options: Dividing and conquering Tracking causes and effects Analyzing and deducing.

satya
Télécharger la présentation

Troubleshooting Techniques

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Troubleshooting Techniques Lesson 29

  2. Skills Matrix

  3. Skill Matrix

  4. Troubleshooting • Develop a systematic approach to solving problems: guessing rarely proves effective. You have options: • Dividing and conquering • Tracking causes and effects • Analyzing and deducing

  5. Troubleshooting the Installation • You need to know why your SQL Server installation failed. • The first place to look when you have problems is in the Windows Event Viewer. • If you do not find the source of your problems in the Event Viewer, navigate to X:\Program Files\Microsoft SQL Server\90\Setup Bootstrap\LOG, open the Summary.txt file, and check for error messages.

  6. Troubleshooting SQL Server • To fix a problem, the logical first step is to determine the cause of the problem, and the best way to do that is by reading the error logs. • Error logs in SQL Server are stored in two places—the first is the SQL Server error logs. • The second place you will find SQL Server error messages is in the Windows Application log.

  7. Troubleshooting Blocks and Deadlocks • Obviously, you do not want other users to be able to make changes to data while you are reading or modifying it yourself. • To keep this from happening, SQL Server automatically places locks on the data that is being accessed to limit what other users can do with that data at the same time. • SQL Server has several types of locks; shared locks and exclusive locks.

  8. Shared Locks • Shared locks are placed on data that is being accessed for read purposes. In other words, when a user executes a SELECT statement, SQL Server places a shared lock on the data requested. • Shared locks allow other users to access the locked data for reading but not modification.

  9. Exclusive Locks • Exclusive locks are placed on data that is being modified. • This means when a user executes an INSERT, UPDATE, or DELETE statement, SQL Server uses an exclusive lock to protect the data. • Exclusive locks do not allow other users to access the locked data for any purpose; the data are exclusively available to the user who placed the lock.

  10. Troubleshooting Blocks and Deadlocks • SQL Server does a great job of dynamically setting these locks, so you don’t need to be concerned with setting them yourself. • What you do need to be concerned with is making sure your queries are properly written so SQL Server does not place locks that get in the users’ way. • The primary cause of this is deadlocks.

  11. Troubleshooting Jobs • You can use jobs to automate tasks in SQL Server. • Jobs are actually a series of steps that occur, one after the other, to accomplish a task.

  12. Troubleshooting Jobs • If one or more of your jobs are not working, check the following: • The SQL Server Agent service must be running for jobs to work. If it is not, start it. • Make sure the job, each step of the job, and each schedule of the job is enabled. • Make sure the owner of the job has all the necessary permissions to run the job. • Check the logic of the job; that is, make sure all the steps fire in the correct order. • You can tell easily whether a job has run successfully by looking at the history to find out when the job last fired.

  13. Dynamic Management Views • Dynamic Management Views and functions return server state information that can be used to monitor the health of a server instance, diagnose problems and tune performance. • There are two types of dynamic management views and functions: • Server-scoped dynamic management views and functions. These require VIEW SERVER STATE permission on the server. • Database-scoped dynamic management views and functions. These require VIEW DATABASE STATE permission on the database.

  14. Information Schema • The Information Schema provides the same information as the system catalog views but also provides a layer of abstraction making the metadata more consistent and easier to work with. • Information Schema views are based on an ISO standard and applications that use them are portable to other database systems that are also ISO compliant.

  15. Performance Studio • Performance Studio stores historical performance data displayed in several different reports. • Examine these reports for clues to performance degradation.

  16. Event Logs • Your operating system maintains the event logs. For the most part you want to examine the application log. • Go to Administrative ToolsEvent ViewerWindows LogsApplication. • Scroll through the entries looking for, primarily, red icons. Double-click the icon to get a descriptive message box. Take appropriate action.

  17. Event Viewer

  18. Server Operational Logs • SQL Server maintains its own logs. Navigate to SQL Server Management StudioManagementSQL Server Logs. • Double-click the current log. Scroll through the log to gain an understanding of what it reports. • Click on the magnifying glass (search). Enter FILESTREAM. See how your system is configured.

  19. SQL Logs

  20. SQL Server Profiler • Microsoft SQL Server Profiler is a graphical user interface to SQL Trace for monitoring an instance of the Database Engine or Analysis Services. • You can capture and save data about each event to a file or table to analyze later.

  21. Operating System Performance • SQL Server and Windows Server provide hundreds a relevant counters to help you decipher the state of your server. • Use these counters to help you troubleshoot your server’s problems. • Use these counters to decide if more memory will solve the performance issue; if an additional CPU will help; if it’s really a network slowdown.

  22. Trace Flags • Use trace flags to set specific server characteristics or to switch off a particular behavior. • In SQL Server, there are two types of trace flags: session and global. • Session trace flags are active for a connection and are visible only to that connection. • Global trace flags are set at the server level and are visible to every connection on the server. • Some flags can only be enabled as global, and some can be enabled at either global or session scope.

  23. DBCC TRACEON and TRACEOFF • To enable a trace flag globally use the Query Editor and use the -1 argument: DBCC TRACEON (2528, -1) • To turn it off: DBCC TRACEOFF (2528, -1)

  24. Startup Options • To enable persistent trace flags, use this example: sqlservr -T1807 • If you use a shortcut, right-click the shortcut icon, choose Properties, and then change the path setting appropriately.

  25. Microsoft SQL Server Database Engine Tuning Advisor • Microsoft SQL Server Database Engine Tuning Advisor helps you select and create an optimal set of indexes, indexed views, and partitions without requiring an expert understanding of the structure of the database or the internals of Microsoft SQL Server.

  26. Job Activity Monitor • The Job Activity Monitor allows you to view the sysjobactivity table by using SQL Server Management Studio. • You can view all jobs on the server, or you can define filters to limit the number of jobs displayed. • You can also sort the job information by clicking on a column heading in the Agent Job Activity grid.

  27. Activity Monitor • Activity Monitor provides information of SQL Server processes and how these processes affect the current instance of SQL Server.

  28. Non-Graphical User Interface • Many less used tools have yet to be converted to GUI displays. • To perform these functions use the Command Prompt of the operating system or PowerShell included in SQL Server.

  29. Single User Mode • You may need to lock out users while you do especially resource-intensive maintenance tasks. • Use PowerShell to run sqlservr –m.

  30. SQLdiag • The SQLdiag utility is a general purpose diagnostics collection utility that can be run as a console application or as a service. • You can use SQLdiag to collect logs and data files from SQL Server and other types of servers, and use it to monitor your servers over time or troubleshoot specific problems with your servers.

  31. Summary • You also learned about troubleshooting problems, which is important because all systems will eventually have problems. • You first learned where to find the SQL Server error logs in Management Studio and how to read them. • You then saw how to find errors in Windows Event Viewer.

  32. Summary • You learned about a new tool starting with SQL Server 2005, the DAC. • This is a special connection that is always open and listening on TCP port 1434. • It allows a single member of the sysadmins server role to connect to an unresponsive SQL Server to run simple diagnostic queries.

  33. Summary • You learned about troubleshooting blocks using the sys.dm_exec_requests system view. • This is a valuable skill because when a session is blocked, it will never execute, and your users will not be able to get their work done. • After that, you learned how to find out when a job last ran in Management Studio. • It may seem simple, but this can save a lot of time when troubleshooting problem jobs. • This lesson also reminded you to use many SQL Server applications and tools to help identify problems.

  34. Summary for Certification Examination • Get familiar with the DAC. The DAC is a special diagnostic connection that is always available for administrators to connect with. • This special connection uses limited resources on the server and is useful for running simple diagnostic commands. • You can use either sqlcmd with the -A switch, or Management Studio with admin: in front of the server name to connect to the DAC. Only one administrator at a time can connect.

  35. Summary for Certification Examination • Know how to troubleshoot. Pick a technique. Be rigorous in your approach. • Use existing tools creatively. • SQL Server answers when you know how to ask. Listen. Solve. • Keep your systems humming and your users content.

  36. Summary for Certification Examination • Know the error logs and locations. SQL Server logs errors in two places on the server: the SQL Server error logs and the Windows event logs. • To find SQL Server errors in the Windows logs, you need to use Event Viewer and look in the Application log. • The SQL Server error logs are best viewed in Management Studio.

  37. Summary for Certification Examination • Know how to use sys.dm_exec_requests to troubleshoot blocks. • Blocks occur when one session has an exclusive lock on an object that another session needs to use. • If the blocking session does not let go of the lock, then the second session will never complete. • This causes problems for your users, so to find the blocking session, you can query the sys.dm_exec_ requests system view and look specifically at the session_id, status, and blocking_session_id columns.

  38. Summary for Certification Examination • Know when to use an available tool from SQL Server's arsenal of troubleshooting weapons. If Profiler is better for a specific issue, don't pick System Monitor.

More Related