SQL Q&A: Monitoring Tools, Memory, and More

Article
08/31/2016

SQL Q&AMonitoring Tools, Memory, and More

Edited by Nancy Michell

Tool Monitoring

Q Is there a tool I can use to monitor a SQL server for locking conditions and alert a DBA when a long, open transaction has occurred?

A The Alerts feature of PerfMon can be used to send alerts for many related SQL conditions, such as deadlock, number of locks exceeding a user-set threshold, and so on. SQL Server™ 2000 provides a host of SQL-related counters to monitor.

For long-running transactions in SQL Server 2000, you could either set the Lock Timeout to detect conditions when blocking is happening for an unusually long time, or you could create a user-defined alert.

Figure 1 Monitoring Categories

Performance
Deadlocks and blocks
Excessive user connections
Pages, writes, and allocations
Active transactions
Average wait time
Database consistency checking (DBCC) logical scan bytes per second
Full scans per second
Memory grants pending
Free space
Connectivity
Database health
Agent jobs
Replication

The SQL Server 2000 Management Pack for Microsoft® Operations Manager (MOM) 2005 may also be able to provide the functionality you are looking for. There are a number of additional and very handy features that MOM will look for, as well, such as availability of key services, replication, backups, and so on. The SQL Server Management Pack monitors conditions that indicate locks and blocks. The MOM 2000 version of the Management Pack monitors the SQL Server performance counters for blocks, deadlocks, and transaction-based counters. It also detects when thresholds for some of these counters have been reached and alerts the operator if an excessive number of deadlocks are occurring in a specified time period. (Both the SQL Server Management Pack and MOM 2000 Management Pack are available at Management Pack and Product Connector Catalog.) MOM 2005 alerts and reports on blocking conditions and informs the user of the blocking and blocked processes. It monitors all the categories listed in Figure 1.

Query Filtering

Q I have two data adapters (daCustomers and daOrders) that get data from Customers and Orders tables. I want to fill my DataSet with a single customer and only those orders that match that customer. As such, I modify the command text of daCustomers to include a WHERE clause so I only get one record back. This works fine. However, I am trying to use a data relation to only get the orders back that match this customer and I have not been successful.

I know I can modify the command text of daOrders since it also has a CustomerID column, but shouldn’t I be able to specify a data relation and subsequently only get those orders based on the relationship? Ultimately I want a DataSet returned that contains a single customer with only those orders that match that customer without modifying the command text of the Orders data adapter.

A You can create one stored procedure that takes a customerid as an input and then returns the fields from the Customers and Orders tables that you want to view. This will return the orders you want:

create procedure GetCustomerOrders
(
    @CustomerID     nchar(5)
)
select <your column needed for display>    
    from customers c
inner join orders o on c.customerid =    
   o.customerid
   where c.customerid = @CustomerID

Then with your data adapter you just pass the stored procedure’s name and set the selectcommand.commandtype = commandtype.storedprocedure, then fill your DataSet and display the results.

Another way would be to fill the two DataSets, add the relation, create a DataView from the Customers DataSet, and then set DataView.RowFilter equal to customerid.

Memory Problems

Q I have 4GB set aside for my SQL Server database and I have Address Windowing Extensions (AWE) enabled, as suggested last time in SQL Q&A. However, I still can’t take advantage of all the memory. What’s wrong?

A We discussed how much disk memory SQL Server can use (see SQL Q&A: Troubleshooting Connections, Using Variables, And More). If AWE is not enabled, SQL Server can only see up to 2GB. However, when AWE has been enabled and more than half of the total system memory is being allocated to a single SQL Server instance, SQL Server may still not see memory larger than 2GB. A hotfix is now available for this problem. Check out "Not All Memory is Available When AWE is Enabled on a Computer Running 32-bit version of SQL Server 2000 SP4 (899761)".

Restoring a Database

Q I am trying to restore a SQL Server database from a device by specifying the location in the Backup Device Location dialog box. However, the option to specify a shared drive doesn’t exist in this dialog box. How can I add the drive path to this location?

A If the SQL Server service is running under a domain credential that has appropriate access to the remote share, you can back up to and restore from a file share via T-SQL BACKUP DATABASE and RESTORE DATABASE. Use the "DISK=" syntax and fully qualify the file name (like this: DISK=‘\\server\share\folder\database_backup.bak’).

Moving a SQL Server Instance

Q I have an active/active cluster pair running two instances of SQL Server 2000, SQL2, and SQL2\i2 on two nodes, NODE1 and NODE2. I’ve been asked to remove one of the cluster SQL Server instances (SQL2\i2), but the server owner still wants to be able to failover drive resources on the second remaining node (for example, SQL2 between NODE1 and NODE2).

If I remove the SQL Server instance, will I still be able to failover by resource? Am I missing something here as this is the production server and I still want to have failover for the remaining instance?

A What used to be called active/active is now called multiple instances. So if you have two virtual SQL Server instances, you have two instances. You could have up to 16 of them. They are completely independent (except for the tools—there is only one copy of them). Removing one instance should not have any effect on the other.

Each instance can be configured to allow one or more of the nodes to be possible owners. So if the remaining instance is configured to allow either Node1 or Node2 to be the owner, then you can fail back and forth. Whether another instance is removed has no effect on the possible owners for the remaining instance.

To summarize, if the virtual instance SQL2 can currently fail back and forth between Node1 and Node2, removing the virtual instance named SQL2\i2 will not change that.

Security Auditing Tools

Q What tools can I use if I want to enhance a security audit of SQL Server?

A You can use the Microsoft Baseline Security Analyzer. It will generate a report of detected security vulnerabilities in SQL Server, Windows, and IIS. You can also use the SQL Profiler tool, which is included with SQL Server, to monitor and capture security-related events and identify any potential gaps in permissions. You might also take a look at the paper "Threat Profiling Microsoft SQL Server" at www.ngssoftware.com for good information on security audits.

Updating SQL Server

Q I need information about SQL Server 7.0 Service Pack scripts to update system catalogs. I have recently upgraded my operating system from Windows NT® 4.0 to Windows® 2000. After the upgrade, I installed SQL Server 7.0 with SP4. Finally, I stopped the SQL Server services and replaced system databases (master, msdb, tempdb) with a file copy, then restarted the services.

I think the server is running with an old system catalog for the master database. If I fix this problem, can I simply rerun the post-SP4 installation scripts instead of reapplying SP4 and restarting?

A If your server is running with SP4 installed and you still have pre-SP4 system databases, you should reapply SP4 to this server. Otherwise this would be considered an unstable environment because the SQL Server executable is expecting options that may not be available. In a best case scenario, the system will act in an unexpected manner.

A service pack would put the server in single-user mode and run these scripts in order to ensure that updating is done correctly. It is unlikely that you could connect with Query Analyzer during production time and run these scripts without them failing or getting some sort of unexpected behavior during or after the scripts run unless SQL Server is restarted.

The service pack puts the server in single-user mode and on shared memory only, runs the scripts in order, and restarts SQL Server normally. To get a stable environment, you should do the same. What you could have done to avoid this situation in the first place is check the SQL Server version before you did any of this and reapplied the exact same version. Now that you have upgraded to SP4, you really will need to schedule downtime to perform this update.

Thanks to the following IT professionals for their technical expertise: Rand Boyd, Bill Emmert, Dave Greene, Cindy Gross, Rob Hawthorne, Scott Heim, Tom Keane, Michael McCracken, Alejandro Mihanovich, Hemant Patel, Ryan Perlman, Rob Schoedel, Ananth Shenoy,Tim Sullivan, and Brad Wilkens.

Additional resources