SQL Questions & AnswersMonitoring DBAs, Replicating Sprocs, and More

Edited by Nancy Michell

Monitoring DBA Activities

Q I have a network administrator who is also a database admin for all the SQL Server™ databases in my enterprise. As such, he has administrative access to the data and data structures of those databases. How can I monitor his administrative activities?

A If you’re using SQL Server 2005, you can use Data Definition Language (DDL) triggers to write events to a log table that you can later review. In SQL Server 2000, you can run a profiler, but that can be resource intensive.

Often these monitoring requirements can be met by having a documented change-control process in place and by removing the built-in administrator role from the sysadmin list, replacing it with a custom group that only contains your DBAs. This would prevent your operating system and domain admins from automatically gaining sa access to SQL Server.

However, while SQL Server 2005 DDL triggers can generate information about schema changes, they won’t capture read or write object access. In SQL Server 2005, you can probably get a lot more mileage from event notifications with very little overhead (for further information, see "DDL Events for Use with Event Notifications").

Running a server-side trace (without using the Profiler UI) may incur an acceptable amount of overhead, but that option first needs to be tested and confirmed. You may be able to use a combination of filters on Application Name, DBUserName, LoginName, and NTUserName for capturing events such as RPC:Completed, SP:Completed, SP:StmtCompleted, SQL:StmtCompleted (these are examples from SQL Server 2000).

With a well-configured trace, you can monitor both read and write access in all databases for selected users or for applications (such as Enterprise Manager and Query Analyzer), while limiting tracing so it captures only the info you want and excludes normal application activity.

Just keep in mind that a user with sysadmin privileges on SQL Server can defeat most forms of database monitoring with enough effort and determination.

Paging Problems

Q I am running SQL Server 2000 SP4 on Windows Server™ 2003 SP1 and I have been investigating some performance problems. I’ve noticed that the Pages/sec and Page Reads/sec counters are very high. The average Pages/sec is 606 and the max is 6469. The Page Reads/sec average is 507 and the maximum is 2090.

The server has 23.5GB of memory with SQL Server configured to use 21.3GB. I changed the memory allocated to SQL Server to 18GB, but I see no change in the values for the Pages/sec and Page Reads/sec. This is a 32-bit environment and SQL Server is running on a dedicated machine. Could either database fragmentation or bad queries be causing the counters to reach such heights?

A When Pages/sec is greater than 0 or Page Reads/sec is greater than 5, Windows is going to disk to resolve memory references (via a hard page fault), which comes at the price of disk I/O and CPU resources. Do you have any other applications running on the server apart from SQL Server? If you stop SQL Server what happens? If the problem is disk fragmentation, try using the DBCC SHOWCONTIG tool on specific tables that could be causing the issue, rebuilding those tables if required. If this issue occurs only during a specific query, do some query tuning.

The following is further information on these counters that you may find useful in determining your problems:

When looking at Pages/sec, start investigating if you’re getting over 100 on a system with a slow disk. Please note that the stated values of over 20 pages, which appear in many other sources of documentation, are out of date. On a system with a fast disk subsystem, even 500 pages per second may not create an issue.

Always break up this count into pages output and pages input if the counter is above 100 per second.

Pages/sec represents the number of pages read from the disk and written to the disk in order to resolve memory references to pages that were not in memory at the time of the reference. In other words, it is the sum of Pages Input/sec and Pages Output/sec. This counter includes paging traffic on behalf of the system cache used to access file data for applications. It is the primary counter to observe if you are concerned about excessive memory pressure (thrashing) and the excessive paging that may occur as a result.

This counter, however, also accounts for such activity as the sequential reading of memory-mapped files, whether cached or not. Therefore, you must understand the expected behavior of the processes on your server in order to interpret this counter. The typical red flag for a mapped file reading is the following combination of characteristics:

• A high number of Pages/sec
• A normal (average, relative to the system being monitored) or high number of Available Bytes
• A normal or small number for Paging File: % Usage

In the case of a non-cached memory- mapped file, you will also see normal or low cache (cache fault) activity.

The number of Pages Output/sec is only an issue when disk load becomes an issue. This value is completely meaningless unless it is evaluated in concert with the disk throughput capacity.

Remember, these pages are pages written by an application and need to be written to the disk to resolve memory references to pages that were not in memory at the time of the reference. This is not resource intensive, and as long as disk write time for the logical partition does not exceed 30 percent, you should not see any impact on system performance. The proper way to observe the disk’s write time is to look at its inverse counter; in this case, the disk idle time should be 70 percent or greater.

Pages Output will stress the system but the application will remain unaware of this. An application will only wait on Pages Input and the troubleshooter will need to know what the application tolerance for waiting on Pages Input will be. For example, SQL Server and most applications will tolerate very few Pages Input while Exchange Server will do much better. Again, you will need a good baseline for comparison.

If you suspect paging is the issue, the best threshold value to use for diagnosing the problem is disk read time on the logical disk that holds the page file. To rule out paging as an issue, look for disk idle times of greater than 85 percent and transfer times of less than 15 ms.

It is important that you know that the pages in and pages out represent writing to the page file and not some data file reading and writing to another file. This is bit difficult except on an enterprise installation where you have different disk partitions for each type of file—for example, the page file is on a partition by itself or on the system partition, the TEMPDB file is on another partition, and all the SQL Server files on another partition.

Look at the disk counter for logical disks, then take the bytes per second and divide by 4096 to convert to pages per second and see which disk the I/O is going to in order to determine if this is true paging or data reads and writes.

SQL Server uses paging for some memory operations. If none of the disks with page files have I/O then the remaining operations must be data operation, not process paging.

If you have no other information, follow the general guideline that paging 40 Pages Input/sec per spindle will not slow down most applications.

Replicating Stored Procedures

Q In my current transactional replication I replicate the transaction, but not the stored procedure. It was recommended that I replicate the stored proc itself.

I have a few questions regarding this. If I replicate the sproc, how do I tell replication not to replicate the transactions? Before I publish the sproc, do I need to stop replication, take a snapshot, and then restart? And finally, if the sproc refers to a table on a linked server, will this be performed on the subscriber?

A To answer your first question, when the sproc is executed, SQL Server automatically tags the session with proper information so modifications made inside the sproc are not individually marked for replication. As a result, only the sproc exec statement will be replicated, not the individual Data Manipulation Language (DML) within the sproc.

As for stopping replication, the answer is no, you don’t have to do all that. You can simply add the sproc article just as you do with table articles.

Finally, if you publish the stored procedure sp_foo, ‘exec sp_foo ‘bar’’ will get replicated as such. You need to make sure sp_foo can execute on the subscriber just like it executes on the publisher.

SQL Server Reporting Services

Q I have some domain users who need to view a report that I created in SQL Server 2005 Reporting Services. How do I grant permission to those users so that they can view the report?

A You can either script this using the rs tool, or you can use the Report Manager by following these steps:

1. Go to the folder in Report Manager where you have the report and click the Show Details button in the upper right.
2. Click the Edit icon next to the report you want to share, and then click the Security link on the left.
3. In the Group or username field, enter the DOMAIN\groupname for the group you created and assigned the users to. (You can add users individually, but groups are easier to manage.)
4. Put a check next to the Browser role and click OK. And now you’re all set.

Monitor Table Statistics

Q I need to issue a list of all tables that have more than 10,000 rows for a specific application. In that list, I want to identify possible candidate columns for indexes based on auto-generated statistics. I know I can list these statistics using the sp_helptstats Stored Procedure in SQL Server 2000 and then run DBCC SHOW_STATISTICS to see the data distribution. But I don’t want to do that. Is there an alternative?

A The best way to do something like this is to configure a trace to capture the SP:Starting, SP:Completed, and Showplan:Statistics events. (There are others you may also have to capture to collect the events for remote procedure calls and T-SQL events.)

Run the trace at least three times during your operational day—during slow, moderate, and heavy traffic times. Take the output trace files and feed them to the Index Tuning Wizard (ITW) as workload files. The recommendations made by the ITW are not absolute, but they should point you in the right direction. Running this sort of trace periodically is really the best way to keep the indexing and statistics in tune.

SQL Server Memory Allocation

Q I recall there being an issue in SQL Server 2000 that if a max memory setting was configured, SQL Server allocated the entire max amount at startup. Is this problem fixed in SQL Server 2005?

A Unfortunately, this is a misconception. Configuring max server memory alone does not make SQL Server allocate all the memory at startup—not under SQL Server 2000, and not under SQL Server 2005. But, depending on the real load, the configured amount can be consumed very quickly.

There are, however, other settings that would result in switching of the dynamic memory management—for example, turning on address windowing extensions (AWE) support. The good news is this has changed in SQL Server 2005!

If you run SQL Server 2005 under Windows Server 2003 (editions that support AWE), you get dynamic memory management even if you switch on AWE support. On almost all other configurations, SQL Server switches dynamic memory management off as soon as you switch AWE on.

Dynamic AWE memory is only available with SQL Server 2005 on Windows XP and Windows Server 2003 or higher. After changing the AWE option, you need to restart the SQL Server process; the decision of whether to use static AWE memory allocation or dynamic AWE memory allocation is made at server start-up time.

It’s also worth noting that if you’re using SQL Server 2005 on 64-bit systems, and the account used to run SQL Server has the "Lock pages in memory" privilege, SQL Server will default to using AWE memory even though the AWE configuration option is not available on 64-bit systems. See the blog by Slava Oks at blogs.msdn.com/slavao/archive/category/9005.aspx for more details on this.

The difference between static memory allocation and dynamic memory allocation is that with dynamic memory allocation, SQL Server responds to changes to min and max server memory without having to restart SQL Server. SQL Server will also respond to the operating system low memory notifications and release memory back to the OS when necessary.

Alternatively, you can configure and run the data collection utility Pssdiag. This approach provides additional performance data about the OS and hardware. This data can be used for an ongoing historical report of server health.

If SQL Server 2005 is an option for your data collection and diagnostics, then you should note that there is a collection of Dynamic Management Views (DMV) to help you find cases where an index would have been useful. If the optimizer finds a case in which an index would have been useful, it makes note of this, detailing which column(s) should be included and whether the columns need to be index key columns or just included columns. It even provides rough estimates of the improvement that might be gained by adding an index. This estimate is based solely on that specific query and without optimizing the workload as a whole (the Database Tuning Advisor, on the other hand, would take the optimized workload into consideration) but it gives you an idea of whether the index is worth pursuing. (See msdn2.microsoft.com/en-us/library/ms345421.aspx for more information.)

Rebuilding Statistics

Q I need to rebuild statistics on very large SQL Server tables that are located in heavy usage environments where there is no server downtime. I would rather not play around with different sample sizes. I’m running SQL Server 2000 and SQL Server 2005. There seems to be less of a need for explicit sampling in SQL Server 2005 since the default sample size has increased dramatically. When is it necessary to do a full scan on a table?

A If the sample is good, there is usually no need to play with the sample size and the default sample will be sufficient.

But, statistical estimations based on sampling depend on the randomness of the sample. A non-random sample will yield bad results, and without randomness, increasing the sampling rate won’t help. The only proven workaround is to avoid sampling altogether and do full scan statistics.

Unfortunately, there is no simple way to determine the randomness of the sample. It usually takes some statistical analysis of the involved data. For instance, to get a sample on column B, you use an index on column A. If A and B are correlated in any way, the sample isn’t random. Other sources of non-randomness come from the fact that you do page sampling for performance reasons.

So, start with default sampling. Try full scan only if you observe performance issues (slow plans). If this works, come up with a plan for periodically refreshing the statistics of the full scan. Also keep in mind that at least on SQL Server 2005, full scan stats get parallelized. Depending on your circumstances and hardware this might dramatically reduce the build time.

Online Resources

• Get SQL Server 2005 Service Pack 1
• Try it out in SQL Server Virtual Labs
• Learn about Full-Text Search in SQL Server
• Build a small installation and hone your skills with SQL Server 2005 Express Edition

Thanks to the following Microsoft IT pros for their technical expertise: Maria Alex, Ben Christenbury, Richard Dawson, Keith Elmore, Michael Epprecht, Qun Guo, George Heynen, Allan Hirt, Cynthia Keough-Erdmann, Simona Marin, Jaime McGeathy, Nitie Mehta, Alejandro Mihanovich, Nagesh Nayak, Igor Pagliai, Uttam Parui, David Reinhold, Craig Riter, Franz Robeller, Steven Wort, Jason Wu, and Peter Zabback.

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; reproduction in part or in whole without permission is prohibited.