Introduction


From the outset of the first stages of the database design, you will be making decisions that will directly affect performance. To look at the hardware configuration, operating system and other analysis tools are available as part of the NT and Windows 2000 operating system. To monitor hardware and operating system events, you would use the System Monitor tool.

The System Monitor enables you to monitor server performance and activity using predefined objects and counters, or user-defined counters to monitor events. System Monitor is referred to as Performance Monitor in Windows NT. Its role is to collect counts rather than data about the events. You can set thresholds on specific counters to generate alerts and notify the individuals who need to address the problem. Often the best way to start the troubleshooting process is to observe the current activity.

The Current Activity window is found in the SQL Server Enterprise Manager and graphically displays information about processes currently running. It also displays blocked processes, locks, user activity, and other useful information for ad hoc views of current activity. Equally helpful are the logs that SQL Server provides for the reporting of errors. Error logs contain additional information about events in SQL Server beyond what is available elsewhere. You can use the information in the error log to troubleshoot SQL Serverrelated problems. The Windows application event log provides an overall picture of events occurring on the Windows NT 4.0 and Windows 2000 system as a whole, as well as events in SQL Server, SQL Server Agent, and Full-Text Search. Several stored procedures have been built into SQL Server to provide for specific benefits.

Monitoring and troubleshooting have always played an important part in the activities performed on the job. Microsoft has always made this an exam focus as well. It is important to understand what each of the tools can do to aid in monitoring and troubleshooting. Knowing which tool to select based on symptoms of the problems is the focus of this chapter, and you'll go into further depth in each of these areas.


System functions and system statistical functions enable you to perform operations and return information about values, objects, settings, and statistical information about the system. System functions are also available to find out more information pertaining to the server, databases, objects, and current session.

Many tools are available to assist with the tuning of your database after you begin the implementation, but don't forget about the design from the outset. Many performance gains will come out of the database design itself. The table structures and relationships, indexing, and other physical objects weigh in heavily when contributing to performance gains. You need to consider controlling the environment right from the beginning.

Concurrency and Locking

One of the hallmarks of a true database management system is whether it has the capability to handle more than one user performing simultaneous data modifications. The problem is that when several users in a database make changes, it's likely that they eventually will want to update the same record at the same time. To avoid the problems this would cause, SQL Server and most database management systems provide a locking mechanism.

A locking mechanism provides a way to "check out" a particular row or set of rows from the database, marking them so that they cannot be changed by another user until the connection is finished and the changes are made. For connections that are reading data, locking provides a mechanism to prevent other connections from changing the data for the duration of the read or longer. There are two basic types of locks: shared locks and exclusive locks. A shared lock happens when a user is trying to read a row of data; for some duration, depending on the transaction isolation level (which is covered later in this chapter), the user owns a shared lock on the table. Because the user is just trying to read the record, there can be several shared locks on the row, so many people can read the same record at the same time.

Users obtain exclusive locks when the user needs to change the row. Exclusive locks are not shared; there can be only one user with an exclusive lock on a row at any given time. If a user needs to acquire an exclusive lock to a row that is already locked by another user, the result is lock contention. Some level of contention is normal in a database that is being frequently updated. Typically, an application waits for some arbitrary amount of time for the locks to clear and the transaction to complete. This results in an apparent slowdown of the application and the server, and excessive amounts of contention lead to performance degradation and possibly user complaints.

There are a few things you can do to reduce lock contention. First, make transactions as simple as possible. Keep extraneous logic out of the transaction. The best case is when you do all the gathering of data and validation of that data outside of the transaction, and the transaction is used only to update and insert rows. Second, you should make sure that the application does not have any transactions that wait for user input because users tend to do such things as go to lunch, usually while they have windows open, waiting for them to enter data to complete their transactions.

In general, you should try to collect all the data at once, and then start the transaction, make the changes, and commit. Design applications and databases with concurrency in mind. Keep tables that are frequently updated small by moving columns that don't belong in the table or that aren't changed as often into another table. If a table is going to be updated frequently, make sure that it isn't indexed more than necessary. Data modification statements, such as INSERT, UPDATE, and DELETE, have to change the indexes as they go, so having too many indexes on a table requires them to modify several indexes.

With contention also comes a circumstance known as a deadlock. A deadlock occurs when two processes are locking resources and each one wants the resource that the other has locked away. Sounds like the makings of a street fight to me. Deadlocks occur when two or more transactions cannot complete because of mutual locks. SQL Server detects the deadlock and more or less randomly kills one of the user processes.

Which process is killed in a deadlock isn't exactly random. If one user is the System Administrator and the other one is just a normal user, the normal user's process is terminated. Otherwise, SQL Server picks the user that has the least to lose from having its transaction terminated. If the users stand to lose equally, SQL Server picks one at random. It's also possible to set a connection-level parameter with SET DEADLOCK_PRIORITY LOW to tell SQL Server that the transaction can be terminated if it is involved in a deadlock.

Controlling Lock Behavior

To avoid deadlocks, make sure that all the objects are always accessed in the same order. Make sure that in cases in which a series of updates to different tables are done, they are always done in the same order. Keep transactions as short as possible, prevent user interaction within transactions, and set a low isolation level. Use a transaction setting as in the following example:

 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 

SQL Server knows that sometimes it's critical that the data you are reading from the database is absolutely 100% committed data, whereas at other times you want the data to be read quickly, and incomplete or uncommitted transactions just don't matter. To accommodate these situations, SQL Server supports four transaction isolation levels:

  • Read Uncommitted This isolation level shows you all the data without getting a shared lock first. Another connection may change the data while it is being read. This can be great for applications that are doing system monitoring or reporting, when minimal impact to the rest of the system is desired. This is also called "dirty reads."

  • Read Committed This isolation level acquires a shared lock during the read of the data but doesn't keep the shared lock for the entire transaction. The resulting data is complete but may change after successive reads, showing new data or indicating missing data with each successive read. This is the default transaction isolation level, and it is generally an acceptable trade-off between reading dirty data and minimizing contention.

  • Repeatable Read This isolation level acquires a shared lock on the rows for the duration of the transaction but still allows other users to add rows into the resultset. That means that later reads may contain more data, but they won't contain any less.

  • Serializable This isolation level acquires a shared lock on the entire range of data being queried, preventing inserts or updates from happening for the duration of the transaction. This is a very dangerous thing to do from a concurrency perspective, because it generates a lot of locks and can more easily result in deadlock problems.

There is one more aspect of locking to discuss. SQL Server also has the capability to lock objects at different levels to increase performance. This is called lock granularity.

In addition to shared and exclusive locks, SQL Server also locks objects at different levels. SQL Server can lock a single row of a table, a single data page, or an entire table.

Typically, SQL Server operates in the page lock mode, in which it locks the data pages being requested. After a certain amount of blocking is noticed, SQL Server slips into a row locking mode, in which single rows are locked.

On the other end of the scale, when a connection attempts to update a certain percentage of a table, SQL Server automatically escalates to a table lock, in which it automatically locks the entire table either exclusively (in the case of a full table update) or shared (in the case of a full table read). SQL Server also determines lock escalation based on the activity occurring in the table at the time of the lock request. If the activity level is low, it saves itself some time by just escalating the lock sooner because it will have less effect on other users.

That means there are shared page locks, shared row locks, and shared table locks for reads, along with exclusive page locks, exclusive row locks, and exclusive table locks for writes. Locks and the control of locking behavior is important in DBMS systems. To aid in management over the DBMS, you can also implement transactions that will directly affect locking behavior. A transaction implies several things:

  • Atomicity A transaction cannot be broken up into smaller units; either the entire transaction happens or none of it happens.

  • Consistency A completed transaction leaves the database in a consistent state.

  • Isolation A transaction is isolated from other transactions in the database, so transactions can't overwrite each other's data. Transactions, in other words, can't interfere with other transactions that are running concurrently.

  • Durability A transaction, after it has been applied, sticks in the database.

These qualities are easy to recall by remembering the word "ACID": Atomicity, Consistency, Isolation, and Durability. SQL Server provides the atomicity, isolation, and durability for you, but it's up to you to make sure that a transaction leaves the database in a consistent state. Atomicity and durability are handled by the transaction logging system and, to an extent, by the lazy writer mechanism of waiting for idle time to commit data. Isolation is handled by the lock manager.

Controlling Business Logic Using Transactions

Transactions provide control over the operations that are being performed. In SQL Server this control can be implied or left to the developer to explicitly request. Either way, you can gain a little control over the changes occurring on the data.

It is possible within SQL Server for everything to be controlled within the realm of a transaction. The SET IMPLICIT_TRANSACTIONS command can be used to turn this device on or off. If you don't explicitly tell SQL Server to treat a group of statements as a transaction, it implicitly puts each statement in its own transaction. For the purposes of an implicit transaction, the only statements that really count are the statements that interact with a database: SELECT, INSERT, UPDATE, and DELETE.

To explicitly put a group of statements into a transaction, you can use the BEGIN TRANSACTION command. This command tells SQL Server all commands that follow up until the end of the transaction, which is noted with a COMMIT TRANSACTION. In the event of a problem with the data being manipulated, you can also call ROLLBACK TRANSACTION. If there is an error during the execution of the transaction, such as a server shutdown, a disk error of some type, or lock contention, then the transaction automatically rolls back.

Here's an example to help you understand why transactions need to be used. Let's say you have a bank database, and it has a Deposit table and a Withdrawal table. Somebody wants to transfer funds from one account, account number 42, into another account, account number 64. Here's how that would look:

 INSERT INTO Deposit VALUES (64, 100.00) INSERT INTO Withdrawal VALUES (42, 100.00) 

That looks fine. However, there will be a significant issue if a failure occurs between the two INSERT statements. When the administrator brings the server back up, there has been a deposit for $100 into one account, but no withdrawal to match, so the SQL Server just invented $100. To handle this task correctly, these statements should be enclosed in an explicit transaction, like this:

 BEGIN TRANSACTION         INSERT INTO Deposit VALUES (64, 100.00)         INSERT INTO Withdrawal VALUES (42, 100.00) COMMIT TRANSACTION 

SQL Server also has the capability to create nested transactions, in which transactions are inside other transactions. In that case, the global function @@trANCOUNT contains the current nesting level of transactions, which is basically the number of times you've executed a BEGIN TRANSACTION statement.

  • Several statements are not allowed inside an explicit transaction: CREATE DATABASE, ALTER DATABASE, DROP DATABASE, BACKUP DATABASE, RESTORE DATABASE, BACKUP LOG, RESTORE LOG, RECONFIGURE, and UPDATE STATISTICS.

  • If you have nested transactions (transactions started within other transactions) and you call ROLLBACK TRANSACTION, it rolls back all the transactions currently pending.

  • The COMMMIT WORK statement is functionally equivalent to the COMMIT TRANSACTION statement. The ROLLBACK WORK statement is functionally equivalent to the ROLLBACK TRANSACTION statement. These can all be abbreviated down to just COMMIT and ROLLBACK. You can also abbreviate the word trANSACTION to trAN, such as in COMMIT TRAN.

Two other complications need to be thrown into this already semiconfusing pile: naming transactions and savepoints. Anytime you execute a BEGIN TRANSACTION, you can tell SQL Server to assign a name to the transaction. The name for a BEGIN TRANSACTION is fairly superfluous, however, because any rollback still rolls back all pending transactions. Here's an example:

 BEGIN TRANSACTION OuterTransaction         INSERT INTO Deposit VALUES (42, 100.00)         BEGIN TRANSACTION InnerTransaction         INSERT INTO Withdrawal VALUES (100, 37.50)         ROLLBACK TRANSACTION 

You're almost guaranteed to find a question on your test relating to a rollback with a named transaction and how much it rolls back. Just remember: Unless a savepoint is involved, a rollback goes all the way back to the first BEGIN TRANSACTION that wasn't followed by a COMMIT TRANSACTION.


The final ROLLBACK TRANSACTION is still going to roll back all the way to OuterTransaction. As mentioned, this just adds to the complication. You can create a savepoint within a transaction and roll back to that savepoint without rolling back the entire transaction. To create a savepoint within a transaction, use the SAVE TRANSACTION command and name the savepoint. Then when you want to roll back to the savepoint, you can use the savepoint name. The syntax is identical to that of a named transaction. Here's an example:

 BEGIN TRANSACTION OuterTransaction         INSERT INTO Deposit VALUES (42, 100.00)         SAVE TRANSACTION SavePoint1         INSERT INTO Withdrawal VALUES (100, 37.50)         ROLLBACK TRANSACTION SavePoint1 --rolls back to the save point 

If that ROLLBACK TRANSACTION didn't have the savepoint name out there, it would roll back to the beginning of the transaction, just as you'd expect. Although transactions can help control data access, their use within procedures can increase locking and they therefore have to be carefully designed.

Data Access Using Stored Procedures

Stored procedures are used extensively to encapsulate business logic, especially by implementing transactions. Using stored procedures is an effective way to encapsulate an entire transaction into one line of execution: the stored procedure call. Using parameters to pass in data and resultsets to return output is a very common way to make sure that the data that comes in is validated and correct and that the correlations between the data being returned conform to established business rules. There are several benefits to using stored procedures that far outweigh the costs.

In a multitier data architecture, SQL Server can provide both the bottom tier (data storage) and middle tier (business logic) parts of a large-scale, client/server application. With judicious use of stored procedures and triggers, data integrity and business rules can be enforced while also providing performance gains over other coding techniques.

Stored procedures can be faster than equivalent "dynamic" batches because SQL Server can cache the query plan and reuse the query plan. So the code is preparsed, preplanned, and ready to run, eliminating some of the startup time for running a batch, especially a significantly large batch. In this respect you get performance with control over the data access placed at the source.

Typically, programmers write programs and database administrators spend most of their time either writing queries for the programmers or fixing broken queries from the programmers. If the queries are encapsulated into stored procedures, the queries can be changed by the database administrator easily, and the database administrator just has to keep the incoming parameters and outgoing data formatted the same, but can make other changes inside the stored procedure without changing the application code.

Applications that do not use stored procedures for handling data make repeated trips to the database to get little pieces of information, which are then correlated and usually applied to the database. This involves data making several trips across the network, and sometimes involves making and breaking several database connections. This is a lot less efficient than simply telling SQL Server how to handle your data and letting the stored procedure process the data without it leaving the server.

Web Applications and Stored Procedures

If you've worked on web applications before, you've probably realized that most web programmers weren't hired to write good queries; they were hired to write good web pages. By encouraging, or perhaps mandating, the use of stored procedures, you can encapsulate all the queries that the web programmers write into one place with a consistent interface. Then, as the SQL guru you will be when you finish reading this book, you can go through and tweak their queries, make them more efficient, and leave the rowsets that get sent to their application and the parameter list the same. That way, you can rewrite the queries and leave the web pages completely alone.

But coding isn't everything. Optimization of data access begins from the outset. A good database design will go a long way toward achieving an efficient system that responds to user requests in a timely fashion.



    EXAM CRAM 2 Designing and Implementing Databases with SQL Server 2000 Enterprise
    MCAD/MCSE/MCDBA 70-229 Exam Cram 2: Designing & Implementing Databases w/SQL Server 2000 Enterprise Edition
    ISBN: 0789731061
    EAN: 2147483647
    Year: 2005
    Pages: 154

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