Management Studio


As you probably know by now, SQL Server Management Studio is where a DBA spends most of his or her time. In the tool, you can perform most of your management tasks and run queries. It's a major evolution of Enterprise Manager (SQL Server 2000's management interface) and uses a light version of Visual Studio 2005. Since this is a professional-level book, we won't go into every aspect of Management Studio, but we will cover some of the more common and advanced features that you might like to use for administration.

Reports

One of the most impressive enhancements to the SQL Server management environment is the integrated reports that help a DBA in each area of administration. There are two levels of reports (server level and database level) and each runs as a Reporting Services report inside of SQL Server Management Studio. Server-level reports give you information about the instance of SQL Server and the operating system. Database-level reports drill into information about each database. You must have access to each database you wish to report on, or your login must have enough rights to run the server-level report.

Server Reports

You can access server-level reports from the Summary tab in Management Studio, by left-clicking the instance of SQL Server you wish to retrieve information on and then selecting the report from the Report drop-down box in the Summary Window. If the Summary Window is not active, select Summary under the View menu.

A report favorite at the server level is the Server Dashboard, which is shown in Figure 4-6. The Server Dashboard report gives you a wealth of information about your SQL Server 2005 instance:

  • What edition and version you're running of SQL Server

  • Anything for that instance that is not configured to the default SQL Server settings

  • The IO and CPU statistics by type of activity (ad-hoc queries, Reporting Services, and so on)

  • High-level configuration information such as whether the instance is clustered or using AWE

image from book
Figure 4-6

Keep in mind that the server and database reports that you see in Management Studio pull from Dynamic Management Views (DMVs) in some cases. Because of that, the data may only be as current as the last time you started SQL Server. For example, the Server Dashboard has a few graphs that show the CPU usage by type of query. This graph is not historical and instead is only showing you the CPU usage for the period of time that SQL Server has been online.

Database Reports

Database reports operate much like server-level reports but are executed when you have the database selected in Management Studio. With these reports, you can see information that pertains to the database that you have selected. For example, you can see all the transactions currently running against a database, users being blocked, or disk utilization for a given database, as shown in Figure 4-7.

image from book
Figure 4-7

Configuring SQL Server

There are a few ways to configure your SQL Server. One mechanism covered earlier is to use SQL Configuration Manager or Surface Area Configuration. These two features will help you turn on various features and services. They do not, however, help you configure the individual instances. For the database engine, you have two main methods to configure the instance: the sp_configure stored procedure or the Server Properties screen. To access the Server Properties screen, you can right-click the database engine you wish to configure in Management Studio and select Properties. Be careful before altering the configuration of your instance. If you adjust some of these settings, you could affect your instance's performance or security. We'll talk about a few of the more important settings in this section, but we'll cover more throughout the entire book.

Using the Server Properties screen is much more user friendly, but it doesn't provide all the options available to you through sp_configure. The General tab in the Server Properties screen shows you information about your SQL Server instance that cannot be altered, such as the version of SQL Server you currently have installed and whether your instance is clustered. It also provides server information, such as the number of processors and amount of memory on the machine. Keep in mind that just because your server has 2GB of RAM available doesn't mean that all of that RAM is available to SQL Server.

In the Memory page of the Server Properties screen, you can see how much memory SQL Server is configured to use. By default, SQL Server is configured to use as much memory as the OS and the edition of SQL Server will allow it to consume. Typically, it is a good idea in your environment to set a floor of the minimum amount of memory that your instances will start to use. You can also turn on AWE from this screen. AWE enabled SQL Server 32 bit machines to utilize more memory once you pass 4 GB of RAM. (We talk much more about this feature in Chapter 11.)

In the Processors page, you can confine SQL Server to use given processors for I/O or threading operations. This is useful typically if you have eight or more CPUs and more than one instance of SQL Server. You may have one instance use four processors and the other instance use the other four processors. In some cases, when you have a large number of concurrent connections to your SQL Server, you may wish to set the Maximum Worker Threads option. Configuring this to 0 (default) allows SQL Server to automatically and dynamically find the best number of threads to allow on the processor. These threads are used for managing connections and other system functions such as performing CHECKPOINTs. Generally, leaving this setting alone will give you optimal performance. You can also select the SQL Server Priority option to force Windows to assign a higher priority to the SQL Server process. This setting may be tempting to set, but you should only adjust it after thorough testing.

In the Security page, you can adjust whether your SQL Server accepts connections through SQL Server and Windows Authentication or Windows Authentication only. This same question is asked during the setup of the instance, and this screen gives you another opportunity to change the setting. Under the Login Auditing section, you should always have at least Failed Logins Only selected. This will allow SQL Server to audit anytime someone mistypes a password or is trying to force their way into the instance. (We talk much more about the other security settings on this page in Chapter 9.)

In the Connections page (shown in Figure 4-8), you can adjust the default connection properties. One of the handy settings here that is sometimes set is the Query Governor. This setting may be a bit misleading to the DBA. The setting will tell SQL Server that if any SQL Server query is estimated to take more than the specified number of seconds, it will be terminated prior to execution. You can adjust this setting without having to restart your instance, and it can be overridden at a connection level if the connection uses the following syntax and is a sysadmin:

 SET QUERY_GOVERNOR_COST_LIMIT 120 

image from book
Figure 4-8

This query specifies that SQL Server will allow queries to execute estimated to use less than 120 seconds. If any query is sent to SQL Server that is estimated to take longer than this (200 seconds, in my case), the user's query will be cancelled, and he or she will receive the following error:

 Msg 8649, Level 17, State 1, Line 4 The query has been canceled because the estimated cost of this query (200) exceeds the configured threshold of 120. Contact the system administrator. 

Because this setting is using the estimated cost of the query, it may not be perfect. The Query Optimizer may estimate that a query will take two seconds to run, but in actuality it takes 45 seconds sometimes, based on the system conditions. In this screen, you can also set default connection options if someone has not explicitly defined connection settings. A good one to set here would be SET NOCOUNT. This setting will stop the "8 Rows Affected" message from being sent to the client if they do not request it. There is a small performance enhancement by doing this, since this message is an additional recordset sent from SQL Server and may be unneeded traffic.

In the Database Settings page, a potentially great setting to set is the database and log default locations. A best practice is to separate data and log onto different drives. This allows you to get the most out of your RAID system, which we discuss in Chapter 11. The default setting, though, is to place the log and data files on the same drive under the %System Drive%\Program Files\Microsoft SQL Server\MSSQL.1\Data and Log directories.

If you want to configure the instance by using sp_configure, you can start by running the command without any parameters to see the current configuration. When you do this, you will by default only see the basic settings (about 14 as of Service Pack 1). Many settings that you'll want to configure are considered advanced. To see these options, you'll need to configure the instance to display advanced options. You can do this by running sp_configure as shown here:

 sp_configure 'show advanced options', 1 RECONFIGURE 

The setting is not moved from configured value to a running value until the RECONFIGURE command is issued. In some cases, you may have to issue a RECONFIGURE WITH OVERRIDE for some settings that will need a restart of the SQL Server instance. If you need to set one of these settings, you will be notified with a message, as shown here:

 Msg 5807, Level 16, State 1, Line 1 Recovery intervals above 60 minutes not recommended. Use the RECONFIGURE WITH OVERRIDE statement to force this configuration. 

The following code shows you how to issue the override for the setting. After you set this, you will need to restart your SQL Server instance to make it effective.

 EXEC sp_configure 'recovery interval', 90 RECONFIGURE WITH OVERRIDE GO 

Filtering Objects

SQL Server 2000 used an object model called DMO and SQL-NS to retrieve the list of objects for Enterprise Manager. When you had dozens or hundreds of objects to display, it would sometimes take minutes to display the list in Enterprise Manager because the old object models weren't built for showing that many objects. This became a problem when SQL Server implementations began to really scale and SQL Server was adopted by hosting providers and large enterprises that may have hundreds of databases on a single server.

SQL Server 2005 Management Studio uses a new object model called SQL Server Management Objects (SMO), which scales much better than the previous releases of the object model. For example, if you are in Management Studio, you can now expand the list of tables, and while you're waiting for the tree to expand, you can move on to the next task. Whenever the tables are fully ready to display, the menu expands and your task is not interrupted.

You can also filter objects easily, which is useful when you begin to have dozens of objects. To filter the objects in Management Studio, select the node of the tree that you wish to filter and click the Filter icon in the Object Explorer. The Object Explorer Filter Settings dialog box (shown in Figure 4-9) will open, which lets you filter by name, schema, or when the object was created. The operator drop-down box allows you to select how you wish to filter, and then you can type the name in the Value column.

image from book
Figure 4-9

Error Logs

As you probably have already experienced, when something goes wrong with an application, the first thing to get blamed is typically the database. This leaves a DBA to disprove that it's his or her fault. The first thing the DBA does is typically connect to the server and look at the SQL Server instance error logs and then the Windows event logs.

In SQL Server 2005, you can now quickly look through the logs in a consolidated manner using Management Studio. To view the logs, right-click SQL Server Logs under the Management tree and select View SQL Server and Windows Log. This will open the Log File Viewer screen. From this screen, you can check and uncheck log files that you wish to bring into the view. You can consolidate in logs from SQL Server, Agent, Database Mail, and the Windows Event Viewer, shown in Figure 4-10.

image from book
Figure 4-10

In some situations, you may want to merge the logs from several machines into a single view to determine what's causing an application problem. To do this, you can click the Load Log button and browse to your .LOG file. That file could be a Windows error log that's been output to .LOG format or a SQL log from a different instance. For example, you can use this to consolidate all the SQL logs from every instance on a single server to give you a holistic view of all the physical machine's problems.

Activity Monitor

The Activity Monitor gives you a view of connections that are currently active on an instance. The monitor can be used to determine if you have any processes blocking other processes. To open the Activity Monitor in Management Studio, double-click Activity Monitor under the Management tree in the tool.

The tool is a fantastic comprehensive way to view who is connecting to your machine and what they're doing. In the Process Info page (shown in Figure 4-11), you can see each login connecting to your machine (also called a SPID). It's easy to miss how much information is in this window. You can slide left to right to see loads of important data about each connection. It would take way too long to explain each column, but you'll find that most of the columns are very useful to you when debugging is a problem:

  • Process ID: The unique number assigned to a process connected to SQL Server. This is also called a SPID. There will be an icon next to the number that represents what is happening in the connection. If you see an hourglass, you can tell quickly that that process is waiting on or is being blocked by another connection.

  • System Process: Shows you whether processes that are internal SQL Server processes are connected. By default, these processes are filtered out by clicking Filter and must be added back in if you want to see the SQL Server internals.

  • User: The user name in the database that the process is tied to

  • Database: The database the user is connected to

  • Status: Whether the user is active or sleeping. No, this doesn't mean the user is sleeping at his keyboard but instead means that SQL Server keeps the connection active even though there is no activity coming from the login until the user disconnects. Active means the user is performing a query at this exact moment. There are other statuses also, but we'll cover those later in this chapter and when we talk about performance tuning.

  • Open Transactions: Shows the number of transactions the user has open at a given point

  • Command: Shows the type of command currently being executed. For example you may see SELECT, DBCC, INSERT or AWAITING COMMAND here, to name a few. This won't show you the actual query that the user is executing, but it does give you a highlight of what type of activity is being run on your server.

  • Application: Shows the application that the user is using to connect to your instance. This can be set by the developer in the connection string that the application uses.

  • Wait Time: If the process is being blocked or waiting for another process to complete, it will show you how long the process has been waiting. Wait Type shows you what type of event you're awaiting.

  • CPU: The number of processor resources that have been spent on the process since login

  • Login Time: When the process first logged in

  • Last Batch: When the process performed its last query

  • Physical I/O: The amount of disk IO spent on the process since login

  • Memory Usage: The amount of memory that spent on the process since login

  • Host: The login's workstation or server name. This is a really useful item, but in some cases you have a Web server connecting to your SQL Server, which makes this sometimes less important.

  • Blocked By: Shows which user's Process ID (SPID) is blocking this connection

  • Blocking: Shows the quantity of processes blocked by this connection

image from book
Figure 4-11

You can click Refresh to manually refresh the data grid. You can also click View Refresh Settings to set the Activity Monitor to refresh automatically every 60 seconds (or whatever you define). Don't set the refresh to anything too frequent, like every two seconds, as it can cause latency, constantly running queries against active systems. You can also apply filters to show only certain hosts, logins, or connections using greater than a given number of resources. Last, you can sort by a given column by clicking it.

By double-clicking any process, you can see the last query run with the connection. You can also click the Kill Process button from this screen to disconnect the user. Processes can also be disconnected by right-clicking the user and clicking Kill Process from the main Activity Monitor page. The user logged in running a query would then see the following message:

 Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded. 

The Locks by Process and Locks by Object pages allow you to see what tables currently have locks on them, grouped by either the SPID or the object name. The window allows you to select a locked table, and it will then show you each lock on that object. You can then go back to the original screen to kill the connection causing the lock.

To learn how this screen works, you can try to create a lock problem by running the following query in one query window while connected to the AdventureWorks database (make sure you back up the AdventureWorks database before performing these steps):

 BEGIN TRAN DELETE from Production.ProductCostHistory 

This query will return the number of records affected. Note that the query was not intentionally committed. In other words, there is a BEGIN TRAN but no ROLLBACK or COMMIT command. Without that, your table is currently locked. Typically, you may have run queries without a BEGIN TRAN. In these situations, you actually have an implicit transaction, and the transaction is committed as soon as the batch is complete.

Next, without closing the first window, open a new query window and run the following query in the AdventureWorks database:

 SELECT * FROM Production.ProductCostHistory 

This query should never return a result and will appear to hang. Do not close either window. Note that at the bottom of each query window your login is displayed that you are signed in as, and your process ID is in parentheses. While the query windows are open, go ahead and explore the Activity Monitor to see what these connections look like.

Open the Activity Monitor and note that one connection has an hourglass next to it. Right-click that connection and select Kill Process. In the query window behind the Activity Monitor, you will now see the earlier mentioned error. Next, go into the Locks by Object page and select the Production.ProductCostHistory table from the object drop-down box. This shows you each of the key locks currently open for this table.

Note that in the Request Mode column, you see an X, as shown in Figure 4-12. An X means that there is an exclusive lock on that key (in this case, hundreds of key locks). An exclusive lock also means that no one else is allowed to read the data cleanly. If you see a request mode of S, SQL Server has granted a shared lock, which is under most situations harmless and others are allowed to see the same data. A user can request a dirty read of the uncommitted data by adding a hint of WITH (NOLOCK) clause:

 SELECT * FROM Production.ProductCostHistory WITH (NOLOCK) 

image from book
Figure 4-12

The Process ID column lets you know which Process ID is causing the problem. You can capture that ID and then go back to the Process Info page to kill the connection. The Type column lets you know what is currently locked. SQL Server starts at the lowest level of locking, which is a KEY lock, then moves to an EXTENT, TABLE, and lastly DATABASE as the most extensive lock.

Monitoring Processes in T-SQL

You can also monitor the activity of your server via T-SQL. Generally, DBAs prefer this as a quick way to troubleshoot long-running queries or users that complain about slow performance. The reason DBAs typically prefer T-SQL is that the information you can retrieve is much more flexible than the Activity Monitor.

sp_who and sp_who2

The sp_who stored procedure also will return who is connecting to your instance, much like the Activity Monitor. You'll probably find yourself preferring the undocumented sp_who2 stored procedure, though, which gives you much more verbose information about each process. Whichever stored procedure you use, they both accept the same input parameters. For the purpose of this discussion, we'll go into more detail about sp_who2 and know that sp_who shows a subset of the information.

To see all the connections connected to your server, run sp_who2 without any parameters. This displays the same type of information in the Activity Monitor. You can also pass in the parameter of 'active' to see only the active connections to your server:

 sp_who2 'active' 

Lastly, you can pass in the process ID as shown here to see the details about an individual process:

 sp_who2 55 

sys.dm_exec_connections

The sys.dm_exec_connections dynamic management view gives you yet even more information to help you troubleshoot the database engine of SQL Server. This DMV returns a row per session in SQL Server. Because it's a DMV, it's displayed tabularly and allows you to write sophisticated queries against the view to filter out what you don't care about, as shown in this query, which shows only user connections that have performed a write operation:

 SELECT * FROM sys.dm_exec_sessions WHERE is_user_process = 1 AND writes > 0 

In addition to the information you're shown in the earlier mentioned ways to view processes, you see in this DMV how many rows the user has retrieved since they opened the connection, the number of reads, writes, and logical reads. You can also see in this view the settings for each connection and what the last error was, if any.

DBCC INPUTBUFFER

DBCC INPUT BUFFER is a great DBCC command that allows you to see what SQL command an individual process ID is running. The command accepts only a single input parameter, which is the process id for the connection that you'd like to diagnose, as shown in the following query:

 DBCC INPUTBUFFER (53) 

This most important column the command returns is the event SQL command run in the EventInfo column. If you're running a particular batch that's large, you would only see the first 256 characters by default in the query window.

 EventType Parameters EventInfo -------------- ---------- ----------------------------------------------------- Language Event 0          begin tran delete from production.productcosthistory (1 row(s) affected) 

Sys.dm_exec_sql_text

Sometimes you need the results of DBCC INPUTBUFFER in a tabular format. You can use the sys.dm_exec_sql_text dynamic management function to retrieve the text of a particular query. This can be used in conjunction with the sys.dm_exec_query_stats dynamic management view to retrieve the most poorly performing queries across all databases. The query below (the query can be downloaded from this book's page at www.wrox.com) retrieves the number of times a query has executed, the average runtime by CPU and duration, and the text for the query:

 SELECT TOP 10 execution_count [Number of Executions], total_worker_time/execution_count AS [Average CPU Time], Total_Elapsed_Time/execution_count as [Average Elapsed Time], (SELECT SUBSTRING(text,statement_start_offset/2, (CASE WHEN statement_end_offset = -1 then LEN(CONVERT(nvarchar(max), text)) * 2 ELSE statement_end_offset end -statement_start_offset)/2) FROM sys.dm_exec_sql_text(sql_handle)) AS query_text FROM sys.dm_exec_query_stats ORDER BY [Average CPU Time] DESC 

The sys.dm_exec_query_stats DMV also shows loads of other information that can be used. It shows you a line for each query plan that has been run. You can take the sql_handle column from this DMV and use it in the sys.dm_exec_sql_text function. Because this view is at a plan level, when someone changes some of the query's text, it shows the new query as a new line.



Professional SQL Server 2005 Administration
Professional SQL Server 2005 Administration (Wrox Professional Guides)
ISBN: 0470055200
EAN: 2147483647
Year: 2004
Pages: 193

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net