When you talk about Performance problems then fragmentation & database integrity issues are dearest friends of DBA!

As data is modified in the databases, the tables and indexes will become fragmented, which means the data is scattered everywhere in the database. So talking in DBA sense the moral is as more fragmented a clustered or non-clustered index becomes, it is nothing but database engine requires to add more pages to fulfil the same query request. So it is a required practice to defragment these indexes on a regular basis using your own methods or industry best practices. So to check the allocation of all database pages and internal structures based on the regular activity on the database, delete/insert/modify processes and instant statement pops up is DBCC CHECKALLOC. When it is executed an internal database snapshots are created to maintain the transactional consistency during the operation of checkout. To get consistent results I have seen that some of  the adhoc scripts specifies TABLOCK then creation of database snapshot may not occur causing exclusive database lock during the execution of this command which will potentially affect the database concurrency.

Ok, enough talk on index fragmentation and now I'm more concerned on disk fragmentation as a DBA you are more responsiblefor server hardware as well. You must understand the value that professional grade disk defragmentation software brings to their servers. Storage servers can experience high levels of disk thrashing (the constant writing and rewriting of small amounts of data) caused from excessive file fragmentation for database MDF & LDF files. When we refer the operating system based fragmentation then you will see network and application issues have a much more visible impact on the performance of network-based services, especially when problems with those functions are encountered.

Optimal disk performance translates into better ROI, but how can you assure that present hardware can bring it all?

Testing the current hardware disks or involve the hardware vendor to get certfied on the testing pattern it is followed. As you can easily obtain results for impact of server disk defragmentation by looking at common tasks that network servers, both physical and virtual, encounter, ranging from maintenance tasks such as server backup and anti-virus scans.Many out there think that involving the tests of a basic user tasksscha s opening files stored on the host server and virtual machines, and manipulating email are not included, but they must be included too. They will help to add on more fuel to the rigorous database testing such as database queries, index creation, and bulk updates.

So doing such a tests requirement I always depend upon the fantastic tool from Diskeeper which can produce fantastic reports about the file fragmentation on to the root level on the servers. Using this tool you need to perform a benchmark tests involving the hardware and I have performed such benchmarking tests using HP's DL380 G5 equipped with dual quad-core 2.83 GHz Xeon processors, each with a 2x8MB L2 cache, 32 GB of RAM and eleven 72 GB 10,000 RPM SCSI drives attached to an HP Smart Array P400 controller that has a 256 MB cache and that supports both serial attached SCSI and SATA drives. The disk volums varies from 30GB to 170 GB due to the nature of the application data I have to go on such range that keeps the database sizes to a minimum of  500 GB within 7200 RPM locally attached SATA drives and 4 x 500GB for backup purpose only. So this Diskeeper tool helped to achieve such a results by performing 3 levels of fragmentations low, medium and high with  Diskcrusher fragmentation utility to createfragmented files and directories. So the results that are produced are phenominal on the level of fragmentation you’ll encounter in usual production environments and pretty much dependent upon the level of use and types of applications the server deals with.

So to refer about complete testing on SQL Server side, we used 2008 version  with a bulk insert of 500,000 rows of data. The bulk insert is often the fastest
method of getting data into a SQL Server database. Obviously captured the resource usage of the server by using PERFMON (SYSMON) for CPU, Memory and disk related counters. Also the testing of table key creation times are directly related to how much data SQL Server had to touch, and the level of fragmentation that had to be dealt with. SQL Server 2008 does a very good job of managing its databases, but defragmentation shows appreciable
improvement in the performance of tasks such as this with a performance improvement of over 11 percent in the most fragmented environments. Coming to the query execution that the test involved differ primarily due to the amount of data returned by SQL, in response to the query. The tests depict the effects of
manipulating the data on a fragmented drive with peak performance improvements of approximately 21%.

SQL Servers benefit from defragmentation; reading and writing data with either application simply works better when the files are not fragmented. Which means whenever a DBA follows the regular practice of defragmenting the indexes then underlying file defrag methods are easy to manage and you will instantly see the result as is improved performance during the busy times at the server. I see that many such solutions are simply de-tour'd by suggesting to throw more storage resources (hardware) at a problem and it should be the last resort, because it only masks the potential problems that intelligent disk
defragmentation addresses. The reason I mention on Diskeeper is which it reported that server storage levels are consistently exceeding 75 percent or so, due to the affect of transactional and usage of users about the  data off of the servers causing the fragmentation which isn’t a direct result of reduced capacity, the chances for fragmentation increase as free storage space decreases and the operating system is forced to write data into an everincreasing
number of non-contiguous spaces.  The tool comes with the option of manual or auto defragmentation method, by using the automated the same disk volume sees absolutely minimal fragmentation even though it is in continual use by applications and users.

To wrap up the discussion it is better to defragment the operating system files too on regular basis or whenever there is a low server activity on the SQL Server, specifically.

There was an interesting discussion about User Defined Fuctions (UDF) performance and patterns, the actual phenomenon is that UDF has got parameters to pass on during the execution.

Just to talk about introduction of such options in the SQL Server history, stored procedures and views have been available in SQL much longer than UDFs, but each of these objects has their niche in SQL Server development. Stored procedures are great for processing complex SQL logic, securing and controlling access to data, and returning a rowset to a calling routine whether that routine is a VB or any other frontend program or another TSQL batch. Unlike views, stored procedures are compiled, making them ideal candidates to represent and process frequently run SQL statements. Views are great for controlling access to data, but they do it differently than stored procedures. Views are limited to only certain columns and rows from the underlying SELECT statement that generated the view. Thus a view is often used to represent a commonly used SELECT statement that may join several tables, employ a WHERE clause, and expose specific columns. Views are often found in the FROM clause of a SQL statement joined to other tables and views.

The default behaviour of scalar UDFs will be like single statement execution, not like batch one where the difference between them is optimizer will have to pickup the best plan to execute. Due to this SQL Server must execute each function on every row, using any function incurs a cursor like performance penalty.  One way UDFs are simple to complete the task for that reason and can be coverted into single-expression, the actual execution of Scalar UDF will have the performance issue whatever way you may try to optimize the list of TSQL statements. So the actual execution time will vary due to fetching the large number of rows, in a sense 1000 or more. As it works like cursor mechanism, during the followup of each row the UDF is executed which will cause usual performance loss. To add more fuel to the problem if the WHERE clause on that UDF includes any filtering then complete execution will have to move slowly as it must invoke each UDF on each row where it is used in the select list, this happens when creating the results of the query to pass to the next stage of query processing.

Coming to the subjective discussion the 2 logic scenarios to be followed as declare local variables, set values to the variables & issue a query referring to the variables in the query filter. So the execution of batch will have the first 2 steps and then actual part of execution needs to be optimized which is in vaccum as the variable values are unknown which ends up using a hard coded guess (which happens to be 30% selectivity for a range filter). On the other scenario (2) with specified constants in the filter, SQL Server expands the definitions of of the UDFs, substituting the parameter values with the specified constants, and the optimizer will optimize the expanded query with knowledge of the values in the filter. So the actual execution part of UDF will be far better on the second scenario of specified constants will be better as indexes are better used. To construct above I have referred following TSQL statements from Microsoft technet links (revisiting old material):

use northwind;
go

create function dbo.FuncWV(@d as datetime) returns table
as return
select orderid, orderdate, customerid, employeeid
from dbo.orders
where orderdate >= @d;
go

So here if the code has the declaration of variable to a value as follows::

declare @myd as datetime;
set @myd = '19980506';

select orderid, orderdate, customerid, employeeid from dbo.f1(@myd) as t;

So look at the execution plan of above query where you will see the predicate identified the optimize which is the ORDERDATE variable: orderdate >= @myd

So the optimizer is required to fine tune the query where this value in the predicate is unknown at optimization time:

select orderid, orderdate, customerid, employeeid from dbo.orders where orderdate >= ?;

Scalar datatype-returning UDFs can be used in various situations to make the code more maintainable, reusable, and less complex. This can be very useful when the same segment of T-SQL code is used in several places, perhaps by several stored procedures and batch SQL statements.  Every now and often I see that UDF execution involves full table scan (clustered index too) causing performance loss as per the range filter causing the hard-code guess of rows selectivity. As the same discussed above on scenario 2 and to demonstrate the same the above code has been modified to use a constant:

select orderid, orderdate, customerid, employeeid from dbo.f1('19980506') as t;

So the above query's execution plan  will have to predicate identified as per the optimizer, which is ORDERDATE >=.
As per this optimizer already takes on the value in the predicate which helps to fetch selectivity estimate to nearest possible with better performance, such as:

select orderid, orderdate, customerid, employeeid from dbo.orders where orderdate >= '19980506';

When you look at the execution plan the lookup on the orderdate column, calculate the selectivity of the predicate with an option of using index on orderdate column. Also in some cases the optimizer will not use previously cached plan, due to the query statements against the function use similar selectivity ranges. Back in SQL 2000 days UDFs are seen as evil, it seems that SQL Server’s query optimisation fails to deal with UDFs properly. Basically it optimises and builds the execution plan without ever looking inside the UDFs to find out what they’re doing, so in all the optimisation isn’t worth the cycles it consumes.

In some cases when you have nesting within UDF then one catch with nesting functions is that built-in functions that are nondeterministic, such as the getdate function, cannot be nested inside of another UDF (otherwise, a SQL Server error is raised). A nondeterministic function is one which may not return the same result when called multiple times with exactly the same parameters. The getdate function falls into this category since every time it is called, it returns the new current date and time. Another commonly used nondeterministic built-in function is the NewID function. It is also nondeterministic as it always returns a unique GUID and, as such, the NewID function is not allowed to be nested within a UDF. SO as per the BOL discussion talking about table-valued UDFs: Within the category of table-valued UDFs there are two sub-types: inline table value-returning UDFs and multistatement table value-returning UDFs. UDFs that return inline tables return a rowset via the SQL Server table datatype. They are defined with a single SELECT statement making up the body of the function. Inline table value-returning UDFs cannot contain additional T-SQL logic outside of the SQL SELECT statement that defines the table it will return. However, they are simpler to create than UDFs that return multistatement tables since they do not have to define the exact table structure to be returned. UDFs that return inline tables extrapolate the structure of the rowset from the SELECT statement itself.

To sumup the discussion assume that there is a UDF that performs a SQL SELECT statement that takes one second to execute. If this UDF is used in a SELECT or a WHERE clause it will be executed for every row. Thus the time the main query takes to execute could increase drastically depending on such factors as the number of rows evaluated and returned and the types of indices in place. Before using a UDF in this type of situation, carefully weigh the options and do some performance testing.  There are good references on the blogs about the UDF performance, that I would like to refer:

An Introduction to SQL Server Scalar UDFs

Alexander Kuznetsov : Not all UDFs are bad for performance.

Are UDFs Harmful to SQL Server Performance?

Using schema binding to improve SQL Server UDF performance

How many times in a day you observe the TEMPDB in your SQL Server enviornment? 

How many times in a week you monitor the TEMPDB in your SQL Server environment?

Well, these are not an interview questions to ask and as you aware that because TEMPDB is so much more heavily used in SQL Server 2005 than in previous versions, you have to take much more care in managing it.  Talking about a database TEMPDB is no different to any other database, but it has an unique behaviour that its a mutual dependancy with the SQL Server instance which is recreated if you restart the SQL instance and not recovered. So this is where the attributes of TEMPDB as a workspace is refreshed which is used for temporary user objects and internal objects that are explicitly created (on fly) by SQL Server processes.

Another topic I would like to refer that logging for TEMPDB is entirely different as compared to other databases in SQL Server, some of you may assume that there is no logging in TEMPDB, which is not true! Every operation in TEMPDB is logged so that transactions on a temporary objects can be rolled back (until certain point of time). Whereby certain records in the log contain only enough information to rollback a transaction and not to recover it. Talking about objects there are 3 objects within TEMPDB, user objects, internal objects and version store. User objects can be identified by the way of using temporary tables or global temporary tables that are created by the user processes. Also its worth to mention about table variables and table-value functions, whereby the free space availability is most important and you can see the user objects by refering to the system catalog views such as sys.objects and sys.partitions along with sys.allocation_units views. Internal objects are not visible to the user or with usual management studio tool. They are not listed in the catalog views because their metadata is managed/stored in the memory.

Talking about Transactions in the subject line above, you will see that DMVs starting with DM_TRAN_* are related to transactional processing within TEMPDB and other user databases. Few changes to the transactional processing within SQL 2005 brings out the concept of version store and snapshot isolation, as they are related to each other. To refer the same to Oracle world this works as UNDO tablespace, which is a place a previous version of data is stored. Using the transaction isolation level SNAPSHOT is a click of actual situation is taken and transaction referring to read the same data is redirected to this version store. That is why it is most important factor for the DBA to keepup the size and free space availability to the TEMPDB, so you must monitor the tempdb to ensure that there is enough space there to support the transactions asking for records located in the version store. Not only that TEMPDB manages the tasks such as hash operations, eagle spool and table spool operations, temporary objects and so on (your best bet to read about these topics is to refer latest BOL).

To get a glimpse of  data for the transactions within TEMPDB execute:

select * from sys.dm_tran_active_snapshot_database_transactions

select * from sys.dm_tran_database_transactions

To get all the information regarding the user transctions and LSN information refer to sys.dm_tran_locks:

Select * from sys.dm_tran_locks;

--To display virtual table with records in version store itself, a little bit confused because the record information comes in a binary format.

Select * from sys.dm_tran_version_store;

Here is the script that I got it when dealing a problem with MS PSS team:

Select transaction_id, db_name(database_id)as database_name,
database_transaction_begin_time,
case database_transaction_type
when 1 then 'Read/Write'
when 2 then 'Read only'
when 3 then 'system transsction' end as transaction_type,
case database_transaction_state
when 1 then 'not initialized'
when 2 then 'init. but no log records'
when 3 then 'log records'
when 4 then 'prepared'
when 5 then 'committed'
when 6 then 'rolled back'
when 7 then 'being committed' end as transaction_state,
database_transaction_log_record_count,
database_transaction_log_bytes_used,
database_transaction_begin_lsn,
database_transaction_last_lsn,
database_transaction_most_recent_savepoint_lsn,
database_transaction_commit_lsn,
database_transaction_last_rollback_lsn,
database_transaction_next_undo_lsn
from sys.dm_tran_database_transactions
where transaction_id>1000;

Also the reference from Kalen Delaney's site: http://www.insidesqlserver.com/companion/SE_Chapter8code.txt & http://www.insidesqlserver.com/companion/QTO_Chapter6code.txt, too good to get you complete code snippets on finding out the locking & concurrency  and their problems.

So the bottom line is best to keepup the transactions in smaller portions that can keepup the performance of the TEMPDB and overall Applications too. The important point and outcome I get for such action is, it avoids lock contentions and deadlocks on a busy database/SQL instance.

Finally a good reference on TEMPDB basics by Sunil Agarwal here:

Managing TempDB in SQL Server: TempDB Basics (Version Store: Growth and removing stale row versions) and corresponding blog posts there, in addition to TempDB Monitoring and Troubleshooting: Allocation Bottleneck , TempDB Monitoring and Troubleshooting: IO Bottleneck and Managing TempDB in SQL Server: TempDB Configuration links.

You may think on the subject line why TEMPDB is a key factor, a simple term is the size and physical placement of the tempdb database can affect the performance of a system.

Even a simple query requires the access/process to TEMPDB, which is a part of the system-processing load may be taken up with autogrowing tempdb to the size required to support the workload every time you restart the instance of SQL Server.

So if the SQL Server instance TEMPD is spread across multiple files then you need to adjust this percentage based on the speed of the I/O subsystem on which the tempdb files are located. This is required to avoid any sort of latch timeout or file initialization you need to limit the AUTOGROW operation depending upon your disk setup/configuration. As you may be aware in SQL Server engine/architecture the data files can be initialized instantaneously. This allows for fast execution of the previously mentioned file operations, as such it is an advantage of instant file initialization reclaims used disk space without filling that space with zeros.

This is where a DBA must pre-allocate space for all tempdb files by setting the file size to a value large enough to accommodate the typical workload in the environment, how can you achieve this task? You have to monitor the queries that are getting exeuted, monitor the TEMPDB periodically during any changes to the database maintenance scheduled jobs. As any such changes to such processes can trigger the files expansion and pre-sizing will help tempdb from expanding too frequently, which can affect performance. Also there is a good reason to leave the TEMPDB database setting to AUTOGROW as you don't know when a smalll process can even trigger the file expansion that should be used to increase disk space for unplanned exceptions.

To monitor the growth or usage of tempdb SQL Server provides Dynamic Management Views (DMVs) that can provide lot of information with an additional planning of collecting performance counters in Windows System Monitor. Books OnLine provides much of information on such DMV that can help to monitor the TEMPDB:

sys.dm_db_file_space_usage. Returns space usage information for each file in the database. For more information, see sys.dm_db_file_space_usage (Transact-SQL).

sys.dm_db_session_space_usage. Returns page allocation and deallocation activity by session for the database. For more information, see sys.dm_db_session_space_usage (Transact-SQL).

sys.dm_db_task_space_usage. Returns page allocation and deallocation activity by task for the database. For more information, see sys.dm_db_task_space_usage (Transact-SQL).

sys.dm_tran_top_version_generators. Returns a virtual table for the objects producing the most versions in the version store. It groups the top 256 aggregated record lengths by database_id and rowset_id. Use this function to find the largest consumers of the version store. For more information, see sys.dm_tran_top_version_generators (Transact-SQL).

sys.dm_tran_version_store. Returns a virtual table that displays all version records in the common version store. For more information, see sys.dm_tran_version_store (Transact-SQL).

sys.dm_tran_active_snapshot_database_transactions. Returns a virtual table for all active transactions in all databases within the SQL Server instance that use row versioning. System transactions do not appear in this DMV. For more information, see sys.dm_tran_active_snapshot_database_transactions (Transact-SQL).

sys.dm_tran_transactions_snapshot. Returns a virtual table that displays snapshots taken by each transaction. The snapshot contains the sequence number of the active transactions that use row versioning. For more information, see sys.dm_tran_transactions_snapshot (Transact-SQL).

sys.dm_tran_current_transaction. Returns a single row that displays row versioning-related state information of the transaction in the current session. For more information, see sys.dm_tran_current_transaction (Transact-SQL).

sys.dm_tran_current_snapshot. Returns a virtual table that displays all active transactions at the time the current snapshot isolation transaction starts. If the current transaction is using snapshot isolation, this function returns no rows. sys.dm_tran_current_snapshot is similar to sys.dm_tran_transactions_snapshot, except that it returns only the active transactions for the current snapshot. For more information, see sys.dm_tran_current_snapshot (Transact-SQL).

As you may be aware that SQL Server 2005 started to offer such an helpful statistics with the help of DMVs, not only that with the service pack releases there has been few changes about transaction processing. One of them is the introduction of version store concept and snapshot isolation transaction isolation level. these two subjects are completely related. If you are aware about Oracle's feature of undo tablespace, which is a place where the previous version of data is stored. Similar to this feature SQL Server offers the transaction isolation level SNAPSHOT which is a picture of the actual situation is taken and the transaction asking for reading this data is redirected to the version store. So all this information is stored in TEMPDB, which is another good step to keepup the space of TEMPDB in order to get an optimum statistics when they are required.

Lastly, winding up to the PERFMON counters you must followup the SQL Server related counters for system performance impacted by SQL Server processes. You will find these counters under SQLServer:Transactions performance object, further to Free Space in tempdb (KB) to monitor the KB offree space in the tempdb database which will give you a snapshot of requirement of enough free space in tempdb to handle the version store that supports snapshot isolation. One of the Technet article provides the formula of providing estimate of size of version store:

You may have seen the sp_replwriterovarbin - New SQL Server Injection-Based Attack Found vulerability about the new public reports of a vulnerability that could allow remote code execution on systems with supported editions of SQL Server 2000, 2005, 2005 Express Edition, 2000 Desktop Engine (MSDE 2000), and Windows Internal Database (WYukon).

The only exceptional SQL Server systems are SQL Server 7.0 Service Pack 4, SQL Server 2005 Service Pack 3, and SQL Server 2008. MSA961040 highlights that this vulnerability is not exposed anonymously. An attacker would need to either authenticate to exploit the vulnerability or take advantage of a SQL injection vulnerability in a Web application that is able to authenticate and by default, MSDE 2000 and SQL Server 2005 Express do not allow remote connections. An authenticated attacker would need to initiate the attack locally to exploit the vulnerability, so it is always better to tighten the permissions on these databases. Review more information from this  CVE reference link and the general recommendation from Microsoft Security Advisory that all customers should apply the most recent security updates released by Microsoft to help ensure that their systems are protected from attempted exploitation.

As usual the systems that are enabled Automatic Updates will get this windows update, if not you must refer to  Microsoft Security Central site for more information. Here is the workaround referred by MSA961040  link :

Deny permissions on the sp_replwritetovarbin extended stored procedure

Use one of the following procedures:

•

To deny access to the stored procedure, connect to SQL Server as a sysadmin using osql.exe or sqlcmd.exe or through SQL Server Management Studio and execute the following T-SQL script: use master deny execute on sp_replwritetovarbin to public

•

To deny access to the stored procedure using SQL Server administration: • For SQL Server 2000: 1. Connect to SQL Server using Enterprise Manager as a sysadmin 2. From the SQL Server Enterprise Manager window, select the desired server 3. Expand the databases 4. Expand Master 5. Click Extended Stored Procedures. A list of stored procedures appears. 6. From the list of stored procedures, right-click sp_replwritetovarbin and select Properties 7. In the Properties window, click Permissions 8. Under Users/Database Roles/Public, find Public, then click the box in the EXEC column. The box turns into a red X. 9. Click OK twice • For SQL Server 2005: 1. Connect to SQL Server using SQL Server Management Studio as a sysadmin 2. From the Object Explorer window, select the desired server 3. Expand the databases and the system databases 4. Expand Master 5. Expand Programmability 6. Click Extended Stored Procedures. A list of stored procedures appears. 7. From the list of stored procedures, right-click sp_replwritetovarbin and select Properties 8. In the Properties window, click Permissions 9. Click Deny execution beside the desired user IDs and click OK

Impact of Workaround: Disabling the sp_replwritetovarbin extended stored procedure prevents updates to subscription tables by all users. The impact of this workaround only affects customers that use transactional replication with updatable subscriptions. Customers using transactional replication with read-only subscriptions, bi-directional transactional replication, or peer-to-peer transactional replication are not impacted. For more information on transactional replication with updatable subscriptions, see MSDN.

How to undo the workaround

As referred above these steps will reduce the problem by blocking the known attack vectors, as I have also successfully applied on the affected SQL Server systems at my end. For more information refer to this SQL 2005 Security best practices link and list of webcasts on security best practices:

• Authentication and Authorization
• Encryption
• Execution Context
• SQL Server Security Best Practices

PERFMON and Resource Governor - two great tools in hand for a DBA to monitor/resolve the performance problems, agreee!

Back in olden days you may remember about SQLDiag and PSSDiag, the utility used as a general purpose diagnostics collection utility that can be run as a console application or as a service. Both of these Diag tools are helpful 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. SQLdiag is intended to expedite and simplify diagnostic information gathering for Microsoft CSS.

Ok, most of the web resources out there talk pages and pages together about features/utilities you can use, but not how you could make use of these tools (freely available from Microsoft) to do 'a definitive'  type of analysis near the beginning of troubleshooting; for example, if you don't know whether the primary bottleneck on the system is disk or CPU, how do you know whether to focus on Query A (the most expensive query in terms of CPU) or Query B (the most expensive in terms if I/O)? Hence from a getting started to performance monitoring the tool to look forward is the good old PerfMon Tool.

So when going about current used version for instance SQL Server 2005 instance, loads of counters are available that can be related to Database Engine (such as Access Methods, Buffer Cache ratio, Cursor, Locks and Transactions etc.). Talking about this version it is good to refer about Service Broker related and replication (if any involved) related too. Why Service Broker, as it is a set of performance counters that provide information on its activities. Service Broker also logs serious errors to the SQL Server error log and the Windows application event log, a reference from Books Online :

First, use the WAITFOR clause. Messages seldom arrive at predictable intervals. Even in a service where messages arrive at roughly the same rate that the stored procedure processes the messages, there may be times when no messages are available. Therefore, the procedure should use a WAITFOR clause with a RECEIVE statement or with a GET CONVERSATION GROUP statement. Without WAITFOR, these statements return immediately when there are no available messages on the queue. Depending on the implementation of the stored procedure, the procedure may then loop back through the statement, consuming resources needlessly, or the procedure may exit only to be reactivated shortly thereafter, consuming more resources than simply continuing to run.

You allow for the unpredictability in timing by using the WAITFOR clause with the RECEIVE or GET CONVERSATION GROUP statement. If your application runs continuously as a background service, you do not specify a time-out in the WAITFOR statement. If your application is activated by Service Broker, or runs as a scheduled job, you specify a short time-out, for example, 500 milliseconds. An application that uses the WAITFOR statement gracefully handles unpredictable intervals between messages. Likewise, an activated application that exits after a short time-out does not consume resources when there are no messages to process.

So then moving onto SQL Server 2008 specific there are some new counters that get introduced with certain features like Resource Governor (RG). These features are exciting and open new options to developers and administrators when working with SQL Server. RG introduces a whole new concept of Resource Pools  and Workload groups and we have monitors for the same under separate counters set.

Mixing together with PERFMON (SYSMON) counters with Resource Governor will give you much leverage on the performance monitoring concept, lets take a look at the following counters that will help to identify where is the problem:

Above all lets not forget the Dynamic Management Views (DMVs), another mix and match tool with Resource Governor to get more information on the health of SQL instance *& problem resolution.

When the SQL Server is faced to the internet then you have to take utmost care to ensure that the each row in the table is not compromised to avoid any unprecedented activity.

As it is getting popular that rise in SQL injection attacks has reminded the best practices and rules to secure the database & web environment to avoid any escalation in a class of attacks targeting Web sites that use ASP and ASP.NET technologies. I don't need to say that these SQL injection attacks do not exploit a specific software vulnerability, but instead target Web sites that do not follow secure coding practices for accessing and manipulating data stored in a relational database. When a SQL injection attack succeeds, an attacker can compromise data stored in these databases and possibly execute remote code. Clients browsing to a compromised server could be forwarded unknowingly to malicious sites that may install malware on the client machine.

When you have a threat then the user will obviously look for an advisory from the Product Vendor or search on web to deploy best practicies on their side, this is an important aspect that every DBA and System Administrator must follow by identifying and correcting vulnerable ASP and ASP.NET Web application code which does not follow best practices for secure Web application development.

You may have secured the access to the tables but what if the hacker can get hands-on for the user password, this is where you should need a validation process and having such a failure to properly validate user input can allow an attacker to inject SQL commands into input fields, which may then execute against a data source leading to database corruption or code execution on the server. In the recent times we have asked Microsoft TAM many times to provide such an advisory to avoid any sort of mishaps within the data environment.

This is what we have been given the information about several tools that are available to secure the platform such as these tools can cover detection, defense, and identifying possible coding which may be exploited by an attacker.

Few months ago I had an excellent opportunity to learn and share the knowledge within SQL Server performance tuning, Tech-Ed Online talks [remember Tech-ed Online Panel: Leveraging ... ].

Same in the series Amit Bansal, SQL Server MVP from India and myself had a brief chalk-talk online session on query tuning techniques and also brief tips on how you can fine tune a query performance problem. Amit highlights the best usage of query hints to take advantage on performance tuning and I have given brief insight on how you can go about resolving a database performance problem.

Check it out the talk from here: Tech-Ed_Online Talk - Performance-Tuning_Techniques

(You may not open the video if you click on above link, rather you may save locally on your machine and open the same with your media browser)

Hope this will help you to set a baseline your performance techniques.

Format: asx
Duration: --:--

You may be aware that creating a PRIMARY KEY or UNIQUE constraint automatically creates a unique index on the specified columns. There are no significant differences between creating a UNIQUE constraint and creating a unique index independent of a constraint. No difference between the data validation between them and as usual the query optimizer does not differentiate between a unique index created by a constraint or manually created. For the need of data integrity you should consider UNIQUE or PRIMARY KEY constraint and it may not be achievable if  duplicate key values exist in the data. Say if the data is unique and you want uniqueness enforced, creating a unique index instead of a nonunique index on the same combination of columns provides additional information for the query optimizer that can produce more efficient execution plans. For additional optimization it is better to consider creating non-clustered indexes that contains included nonkey column, see Index with Included Columns link.

Filtered indexes can provide the following advantages over full-table indexes:

• Improved query performance and plan quality
  A well-designed filtered index improves query performance and execution plan quality because it is smaller than a full-table nonclustered index and has filtered statistics. The filtered statistics are more accurate than full-table statistics because they cover only the rows in the filtered index.
  
• Reduced index maintenance costs
  An index is maintained only when data manipulation language (DML) statements affect the data in the index. A filtered index reduces index maintenance costs compared with a full-table nonclustered index because it is smaller and is only maintained when the data in the index is affected. It is possible to have a large number of filtered indexes, especially when they contain data that is affected infrequently. Similarly, if a filtered index contains only the frequently affected data, the smaller size of the index reduces the cost of updating the statistics.
  
• Reduced index storage costs
  Creating a filtered index can reduce disk storage for nonclustered indexes when a full-table index is not necessary. You can replace a full-table nonclustered index with multiple filtered indexes without significantly increasing the storage requirements.
  

It is a best practice to include a small number of key or included columns in a filtered index definition, and to incorporate only the columns that are necessary for the query optimizer to choose the filtered index for the query execution plan. The query optimizer can choose a filtered index for the query regardless of whether it does or does not cover the query. However, the query optimizer is more likely to choose a filtered index if it covers the query.

How many tools do you (need/use0 to monitor the performance of your SQL Server instance(s)?

Well, I'm not asking any count on the x number of tools you require to monitor the SQL Server instances within your environment, and Performance Tuning using Management Studio unlike any other tool to figure out to monitor the performance tuning. With SQL Server 2008 tools such as Management Studio (SSMS) can do all on the  management, performance tuning and monitoring etc. aspects. Then it comes a flurry of questions to ask yourself that :

• How many times you have used the Database tuning advisor?
• How many times you have used the Profiler (on day to day/weekly/monthly)?
• How often you use and depend on the DMVs?
• How many times you use the Performance Reports (Standard) in SSMS?
• Do you know how to monitor the performance on your SQL Server?

If you are not a DBA then don't worry, today the amount of resources we have on the cyber-world can get you on wheels to act like a DBA to take hands-on work to monitor your SQL platform. You may be aware that there are new reports to the standard reporting tool on SSMS, check them out and we will see them in the end... 2005 and 2008.

Management Data Warehouse is new kid on SQL Server 2008 town that is loaded with new performance monitoring features, you need a good understanding on the architecture of how SQL Server 2008 collects performance and diagnostic data and presents the information in Management Studio.  For an automated performance monitoring you should develop a plan for enabling performance data collection and analyzing the results for troubleshooting and diagnostics.

For all such questions in addition to the SQL Server 2008 Books ONline and online resources on web about SQL Server 2008 I would like to refer the presentation "Performance Monitoring Using SQL Server Management Studio - power of Management Data Warehouse" that was delivered at SQLBits conference on 13th September in United Kingdom.

I'm sure this will help you to learn how to create your own collection sets and manage the performance data collection warehouse database.

Here is the presentation file (stream) and thanks to Camtasia for making this happen.

Also the powerpoint show - http://sqlserver-qa.net/Satya_SQL2008_PoweofMDW.pps for  your reference.

Format: avi
Duration: 01 Hour 11 Minutes

I believe it has been a while that we have seen the security hotfix from Microsoft Security team on SQL Server (alone). The latest security bulletin has announced important security patches that are related to SQL Server in addition to Windows operating system. They quote that "...With the release of the bulletins for July 2008, this bulletin summary replaces the bulletin advance notification originally issued July 3, 2008. For more information about the bulletin advance notification service, see Microsoft Security Bulletin Advance Notification....".

The Severity rating has been given as important which means you have to test the referred hotfix from this KBA Vulnerabilities in Microsoft SQL Server Could Allow Elevation of Privilege (941203) link. As that goes in specific to SQL Server the following are for Windows alone: Vulnerabilities in DNS Could Allow Spoofing (953230), Vulnerability in Windows Explorer Could Allow Remote Code Execution (950582) & Vulnerabilities in Outlook Web Access for Exchange Server Could Allow Elevation of Privilege (953747) links.

So what is your practice in deploying such security hotfixes within your environment?

Don't forget to test the patches before deploying them on to the production, that might cause unprecedented downtime and with prior testing you will be able to catch the issues in hand. Also it is recommended to deploy Baseline Security Analyzer tool MBSA that  allows administrators to scan local and remote systems for missing security updates as well as common security misconfigurations.

If you are new to this type of alerts or patches then you must visit the Security Guidance for Update Management link that provides additional information about Microsoft’s best-practice recommendations for applying security updates.

Recently I have been involved to carry over an assesment of server utilization on the SQL estate farm (nearly 150+) instances, as it stands it is not an easy job to obtain the results when you have no influence on accessing required information on a shared platform. So in order to get the results quickly as you might guess using PERFMON *(SYSMON) tool is best to go with for initial assesment. As it defines within the SYSMON tool getting Collection of counter logs  with Performance Logs and Alerts option instantly we created set of jobs against business importance  servers that are having multiple SQL Server instances.

I was told that there may be third party tools available from the vendor such as Quest or IDERA with a built-in data-collection model process that can configured for automated data collection against multiple servers across multiple domains, and also Systems Management Server (SMS) from Microsoft also helps to some extent. Neither of these tools were available at our end so the obvious choice is to go with manual precision of using PERFMON tool.

I would like to share the required collection of counters that will be useful to obtain server utilization information if you have similar exercise or process to obtain.

I hope this will help others too.

As a DBA, you probably often find yourself striving or struggling to improve the performance of SQL Server instance queries (might be smaller or complex ones). By default in order to get further analysis on the system's performance you need to perform server side trace along with SYSMON (PERFMON) trace collection, this is important in trying to discover the queries or batches that take too long to run, perform too many I/O requests, or use too many CPU cycles.

By default the users will find using PROFILER than running Script based trace on server side or even with  SQL Server traces in Profiler and sort them by different columns (e.g., duration, cpu, reads, writes), this process tends to be time-consuming and doesn't provide aggregated data. Alternatively, you could load the traces into SQL Server tables and run aggregate queries against these tables to analyze the worst-performing queries in the trace. The Read80Trace utility simplifies trace analysis by automatically creating an analysis database for the trace file(s) you provide. It also generates a graphical HTML output file that contains detailed information about the load captured in the trace files.  

In continuation with the blog SQL Server troubleshooting tools PSSDiag, SQLDiag, SQLNexus, RML Utilities and ReadTrace: which one to choose? here about Precision Performance objective driven by Microsoft CAT team about RML utilities I would like to refer this further explanation blog post from SQLCAT blogs.

More from this Precision Performance for Microsoft SQL Server using RML Utilities 9.0 post.

One of the biggest threats in IT industry & Database world is unprecedented attacks aka most commonly termed as 'SQL injection'.

There is no doubt that biggest database vendors, one of them as Microsoft so far providing recommendations regarding security-related configuration settings since the good & bad times of SQL Server version 2000, not in particular to point DBA or Developer and whole as in Application database, remember 'Slammer Worm'!. Here it is best to refer the ignored 'best' practices (bad)  that are seen at most of the deployments by leaving the blank password for any application connectivity or very common used words such as 'password' or so. In this fashion any generic installation of SQL Server and can be relatively easily configured on the server, database, or database object level and obvious attempt on access to data is provided via client applications, which increases the range of potential vulnerabilities and places an equal share of responsibility for data security on software developers, where I feel most of users must be educated/trained on security aspects.  

So to know more about SQL injection attacks and vulnerabilities within your system I'm providing few best examples and explanation, such as SQL Injection attacks post by Buck Woody &  SQLInjection-Attachs-by-example blog posts.

To close the topic I would like to highlight the importance of monitoring the information (small or big) that might be revealed via error messages resulting from executing malformed SQL statements. It is like leaving your house key to the door lock when you are supposed to secure it when you are going away!