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!

Secured by default, secured by design is the buzzword and catchy stuff for any Enterprise IT administrators. Then it comes about standards and policies to follow such as ISO , with the recent threats (last 10 years) and vulnerabilities within IT world it is most important factor you need to follow as per the compliance standards that are deemed as industry proven, Common Criteria. This 'international' standard has been ratified in the year 1999 that was designed by a group of nations to improve the availability of security-enhanced IT systems, not only standards documentation there is a process in place to evaluate your IT systems on periodic basis in terms of security.

On such terms SQL Server 2005 comes as 'trustworthy' whereby the general policy of 'off by default' or 'enable only when needed' has been implemented by design, that means connectivity-policies/services such as Service Broker, HTTP or Database Mirroring endpoints, XP_CMDSHELL on surface area are disabled by default. Also the network protocols such as TCP/IP, Named pipes are disabled too with an exception to Shared Memory is available/enabled by default. Also the VIA endpoint is disable in addition to discontinuation support for some network protocols that were available with earlier versions of SQL Server, including IPX/SPX, Appletalk, and Banyon Vines.  

Dedicated Administrator Connection (DAC) that is given secret key to DBAs to use when every other connectivity is locked out on SQL instance will also be availabile to use locally 'by default', that means you need to logon remotely to use it and anyhow you can enable it to use it remotely too. To know whether DAC is enabled or not you can refer to SQL Server 2005 detect DAC session with TSQL post here and use the feature responsibly! Be aware that DAC is not available to use in SQL Express edition unless you start that instance with a trace flag 7806.

As referred above permission to database endpoints requires CONNECT permission, as it is also disable by default that will restrict access paths and blocks some known attack vectors. It is a best practice to enable only those protocols that are needed. For example, if TCP/IP is sufficient, there is no need to enable the Named Pipes protocol. As the documentation refers to use Surface Area Configuration which is introduced in SQL Server 2005 has been taken away in upcoming 2008 version (SQL Server 2008: Administer multiple servers by designating configuration servers), so you need to get used to perform such endpoint administration using DDL statements and using SQL Server Configuration Manager that will give you a simplified user interface for enabling or disabling client protocols for a SQL Server instance. This GUI provides more granular configuration of server protocols. With Configuration Manager, you can:

·         Choose a certificate for SSL encryption.

·         Allow only encryption connections from clients.

·         Hide an instance of SQL Server from the server enumeration APIs.

·         Enable and disable TCP/IP, Shared Memory, Named Pipes, and VIA protocols.

·         Configure the name of the pipe each instance of SQL Server will use.

·         Configure a TCP/IP port number that each instance listens on for TCP/IP connections.

·         Choose whether to use TCP/IP dynamic port assignment for named instances.

So to talk about simple and better practices you need to tighten the SQL Server environment with few policies, specific recommendation for relevant needs. In summary I have implemented the following tasks in order to ensure the maximum security practices are obtained when a new platform is designed and not leaving existing platforms too:

Best practices for network connectivity

·         Limit the network protocols supported.

·         Do not enable network protocols unless they are needed.

·         Do not expose a server that is running  SQL Server to the public Internet.

·         Configure named instances of SQL Server to use specific port assignments for TCP/IP rather than dynamic ports.

·         If you must support SQL logins, install an SSL certificate from a trusted certificate authority rather than using SQL Server 2005 self-signed certificates.

·         Use "allow only encrypted connections" only if needed for end-to-end encryption of sensitive sessions.

·         Grant CONNECT permission only on endpoints to logins that need to use them. Explicitly deny CONNECT permission to endpoints that are not needed by users or groups.

By default it is better to visit the SQL Servers on Production environment with a periodical assesment of security policies and whenever a new hotfix or Service pack is released don't forget to test it thorougly to apply.

Whenever a performance issue occurs on the SQL Server database best option for diagnosing and troubleshooting common problems by using publicly available tools such as Profiler, System Monitor (Perfmon), and Dynamic Management Views (DMVs) in SQL Server 2005 (onwards).

What to detect?

• Use SYSMON to detect excessive compiles and recompiles. Technical documentation refers that for this kind of problem use SQL Statistics object that provides counters to monitor compilation and the type of requests that are sent to an instance of SQL Server. The general trend will be the ratio of SQL Recompilations/sec to Batch Requests/sec should be low unless users are submitting ad hoc queries.

What if high value of compiles & recompiles are found?

• If the PerfMon counters indicate a high number of recompiles, then the problem is due to recompilation of stored procedures and also underlying defragmentation of indexes (this will be discussed below). It is obvious that you have seen the recompiles are contributing to the high CPU consumed by SQL Server. So using Profiler trace to find the stored procedures that were being recompiled, this helps to obtain information along with the reason for the recompilation.
• Further one of the Microsoft technical document gives the following TSQL to see all recompile events that were capture in the trace:

select 
    spid,
    StartTime,
    Textdata,
    EventSubclass,
    ObjectID,
    DatabaseID,
    SQLHandle 
from 
    fn_trace_gettable ( 'e:\recompiletrace.trc' , 1)
where 
    EventClass in(37,75,166)

Going forward you can take help of other counters in SYSMOn such as Access Methods object counters that will to monitor how the logical data within the database is accessed. Additionally you could also find the physical access to the database pages on disk is monitored using the Buffer Manager counters. With this monitoring methods in place can help you to determine whether query performance can be improved by adding or modifying indexes, adding or moving partitions, adding files or file groups, defragmenting indexes, or by rewriting queries. So to find out the overall usage of amount of data, indexes and free space within the database you could make use of Access Methods counters. This is where the data volume and fragmentation information can be obtained to see whether they are contributing to the performance issues in addition to excessive recompilations above, by default excessive index fragmentation can impair performance.  

So on the subject to find out the performance issues with a quick execution of steps you could make use of DMVs and for more detailed information about the data volume usage, index fragmentation you can execute the following ones:

• sys.dm_db_index_operational_stats
• sys.dm_db_index_physical_stats
• sys.dm_db_partition_stats
• sys.dm_db_index_usage_stats

Also the usage of TEMPDB is high from SQL Server 2005 onwards, that is explained more from these Working with tempdb in SQL Server 2005 & Optimizing tempdb Performance Technet articles in addition to viewing the following DMVs results:

• sys.dm_db_file_space_usage
• sys.dm_db_task_space_usage
• sys.dm_db_session_space_usage

That will be starting point for you to analyze the performance issue for further assessment.

This may be the typical situation within your database environment where the you might have killed a SPID (Process) that has been running  for long time, without knowing the ROLLBACK operations for such processes.

Say if you are executing a stored procedure which is built with a transactional based statements, by default the relational engine has the ability to control transactions mainly when you have specified a transaction starts and ends. In addition to that it must also be able to correctly handle errors that terminate a transaction before it completes, as in this case the transactions are managed at the connection level. Going back to the basics, say when a transaction is started on a connection all TSQL statements executed on that connection are part of the transaction until the transaction ends. But when you have multiple active results set such as MARS sessions, then that TSQL explicit or implicit transaction becomes a batch-scoped transaction that is managed at the batch level. Once the batch execution is completed and if that batch-scope transaction is not committed or rolled back, then it is automatically rolled back by SQL Server.

Also these transaction modes are managed at the connection level. If one connection changes from one transaction mode to another, it has no effect on the transaction modes of any other connection. For further error handling within that nested transactions level you can effectively include the TSQL TRY…CATCH construct, in this case Error Handling in SQL Server – a Background is the favourite among the SQL world, refer to it to solve your relevant issues. By default within those TSQL statements or stored procedure execution if you haven't specified the savepoint_name or transaction_name  within your ROLLBACK statement then it will roll back all the statements that were executed within the outermost BEGIN TRANSACTIOn statement.

Coming to the subject of killing a long running process, before doing that ensure to check what that process is executing by uisng DBCC INPUTBUFFER and if you don't get complete information of that process, then refer to Dig-Further_DBCC_INPUTBUFFER  process here. Also ensure to check whether any changes happening on CPU & IO against that SPID when you execute SP_WHO2 to get information. If it is compulsory to kill that SPID then ensure to execute KILL spid WITH STATUSONLY to see the rollback progress. Say if you have execute the above specified statement then you will see the information as follows :

SPID xx: transaction rollback in progress. Estimated rollback completion: 100%. Estimated time remaining: 0 seconds.

Bear in mind that as the rollback process will also take same time as that query execution or even more than that due to the underlying transaction log process that needs to be completed. It is a general assumption by the users that if you use SIMPLE recovery model on database it will take care the log sizes, well it is not. It will truncate the log at every checkpoint but it will not when the rollback transaction has to fillup 60% of the current log size then it will wait to increase the Transaction log file for that database which will have slow process affect to get this process complete in addition to the current processes that are executing, hence the ROLLBACK process is not changing or you might see the blocking too.

So in order to kill the ROLLBACK status process without restarting SQL Server services or reboot the server the only way is to find the kill the KPID of that SPID by running :

--SQL 2005 

select * from sys.sysprocesses

or

--SQL 2000

select * from sysprocesses

Adding password complexity and password expiration to SQL Server logins is one of best feature I quote for SQL Server version 2005 onwards, similarly such password complexity policies are designed to deter brute force attacks by increasing the number of possible passwords. When password complexity policy is enforced and password expiration policies are used to manage the lifespan of a password.

In any of the above cases the SQL Server engine runs the check to enforce password expiration policy and check in order to ensure the users are reminded to change old passwords, and accounts that have expired passwords are disabled. Similar to this you may get the error mentioned below:

Property IsLocked is not available for Login '[x]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo) 
 

Previously I blogged here to solve this problem over here -  LoginPropertyLocked and this issue occured on our side when a SQL instance is  installed with Windows Authentication and then switch to SQL Server Authentication, sometimes it  may not come into affect unless the SQL Server services are restarted. Further questions can arise such as will this happen on the instances which is installed with SQL Server Authentication or any chance in performing SQL Server installer to have any settings to turn off the check password policy, the answer is not quite easy. Say if you need to disable the check password policy on that SQL instance then you have to use below TSQL:

alter login sa [X] with password = 'yourpwd' unlock, check_policy = off, check_expiration = off

Further you have to ensure to disable password policy when using sp_addlogin, as this is deprecated from SQL 2005 onwards and you should continue to use CREATE LOGIN instead, as sp_addlogin statement. Also the Policy Enforcement can  be configured separately for each SQL Server login. Use ALTER LOGIN to configure the password policy options of a SQL Server login. When they are ON then relational engine sets the CHECK_EXPIRATION is also set to ON unless it is explicitly set to OFF, and to the  password history is initialized with the value of the current password hash. Whereas when the  CHECK_POLICY is changed to OFF, then CHECK_EXPIRATION is also set to OFF having the password history is cleared too! So ensure to use this sensibly within your Production server instance.

In few cases when SQL Server is running on Windows 2000, setting CHECK_POLICY = ON will prevent the creation of passwords when used NULL or empty. Also affects if you try to create a SQL login with the same as name of computer or login. So in the case of using the hashed password when creating the login, the password policy cannot be checked for complexity on such password, as the system cannot get the original password from the system and only this is available in hash, but the gain is you this will come into affect since that point of creation, that means when the user triesto change the password during next attempt or time. I recommend to refer to http://blogs.msdn.com/lcris/archive/2007/04/30/sql-server-2005-about-login-password-hashes.aspx blog post for such information.

Further Books online confirms that within  Windows Server 2003 a known issue that might prevent the bad password count from being reset after LockoutThreshold has been reached. This might cause an immediate lockout on subsequent failed login attempts. You can manually reset the bad password count by briefly setting CHECK_POLICY = OFF, followed by CHECK_POLICY = ON. During the recent usergroup meeting one user asked that is there a method in SQL 2005 to globally disable the CHECK_POLICY option? The answer is CHECK_POLICY cannot be disabled globally. You will have to do it at the login-level only that means when a single CREATE LOGIN statement is used, as you cannot use that in a transaction. Also I have seen such issues when you are using a web based application within ASP.NET stating the sql login is failed though it has relevant permissions set,  and in this case I suggest to refer the notes from BOL (again):

MUST_CHANGE
Applies to SQL Server logins only. If this option is included, SQL Server will prompt the user for a new password the first time the new login is used.

CHECK_EXPIRATION = { ON | OFF }
Applies to SQL Server logins only. Specifies whether password expiration policy should be enforced on this login. The default value is OFF.

If MUST_CHANGE is specified, CHECK_EXPIRATION and CHECK_POLICY must be set to ON. Otherwise, the statement will fail.

A combination of CHECK_POLICY = OFF and CHECK_EXPIRATION = ON is not supported.

When CHECK_POLICY is set to OFF, lockout_time is reset and CHECK_EXPIRATION is set to OFF.


CHECK_POLICY = { ON | OFF }
Applies to SQL Server logins only. Specifies that the Windows password policies of the computer on which SQL Server is running should be enforced on this login. The default value is ON.

In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly!

Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases ETL processes, coming to the point by design the DBCC SHRINKFILE operation is a single-threaded operation that means you cannot define or configure the server to use multiple CPUs or a dedicated CPU. So troubleshooting the performance problems on a database system is very tricky, more important is where to look for a problem and for instance it is frustating to see why system reacts in such a bad way even for a simple query execution and this is where you need to look at how CPU, Memory & disks are performing during this operation

Coming to the subject the referred DBCC operation needs to perform the exercise of moving database pages from tail of the file to the beginning in order on the data file, with one page at a time though. SO you need to be careful to select this operation on a huge database as it will tend get the server down to its knees for a single simple-query execution, also the SHRINKFILE operation often make it defragmentation from bad to worse and in many situations I have seen this increases the file logical fragmentation whereby you will see huge difference in performance to produce few hundreds of rows result set.

If you see such performance issue then look at from a high level, there are two paths to identifying CPU performance problems. The first is reviewing the system's hardware performance, an exercise that helps determine where to look when you head down the second path, reviewing the server's query efficiency. This second path is usually more effective in identifying SQL Server performance issues. Unless you know exactly where your query performance issues lie, however, you should always start with a system performance evaluation that will lead to go on both of these routes. On such SQL Server instances where the relational engine actually handles its own extremely efficient queuing and threading to the Operating Sysstem and having CPUs with hyper-threading is a common scenario, this will have the affect to overload the physical CPUs on systems with already high CPU utilization. This is where the threads operation converted as queues from SQL Server with multiple requests to perform work on multiple schedulers. This is where the Operating System struggles to cope up to switch the context of the threads back and forth on the physical processors to satisfy the requests that are being made even if the two logical processors are sitting on top of the same physical processor.

Taking back to the SHRINKFILE operation on the database table(s) with a clustered index(es) on a huge table (rows) then you will see much degraded performance because of the heaps and those heaps have many non-clustered indexes, where it is different to the  clustered index situation. In this case the SHRINK operation of moving the pages having with IMAGE data or Large Object Blob will be too slow, as it has to read the data from each page to arrange. Further the most content of an index/table resides at the end of the file, you can rebuild the indexes to move them to the front end of the file. Also bear in mind to keep a close watch of Transaction log space on the databases in addition to the TEMPDB which is used extensively in SQL Server 2005 version.

So by the end you should ensure to schedule the SHRINK operation during the less traffic hours and when server consists with multipe CPUs to take advantage of performance to finish the operation in timely manner.

What kind of performance issues you see on day-to-day basis within your environment?

The following elements are important factors that can add fuel to the fire (problem):

I/O is important factor to consider when it comes to the performance issues, such as blocking, locking and slow response time that will cause stretching of resources on the server. In this case using SYSMON and other monitoring tools will get you the latch contention and timeouts to the application. So what are these latches and why they are important when it comes to performance, also important for indexes.

A latch is a lightweight version of a lock, in generate they do not hinder performance or concurrency and in any case if you are having performance issues then using SYSMON for latch wait timeouts to determine is inital stage to monitor. By default the latch wait time is a good indication for your hardware disks performance, as it will tell about a process that is taking how long to access data, read and write from the disk. For instance if a latch wait time of 1.3 seconds is telling you that your disk subsystem is a bottleneck or it could be a poor design of the disk subsystem, or from table scans or from choosing the improper RAID type or from just a lack of spindles to achive the IO that SQL is asking for.

Using SQL Server 2005 the DMVs can be handy to get more information on system internals behaviour, so for the Latch Wait Times these relevant DMVs are as follows, such as dm_exec_requests that will get you wait type and wait resources, dm_os_latch_stats for wait counts on BUF and nonbuf latches. Also the dm_wait_stats will get you granular information on latches, further if you have disk related issues then using dm_io_pending_io_requests DMV can get you how the snapshot IO requests are performing with additional information by using dm_io_virtual_file_stats to get per file.

So to know more about these contention and information on I/O that will need to fine tune, refer to SQL2005-IOBasics whitepaper, SQL Server performance monitoring requirement and specifications, heard about baseline and benchmarking?  and methods of Identify and troubleshoot slow running queries in SQL Server blog posts here.

Paging and Memory bottlenecks are quite common when a performance problem strikes on your SQL Server. As we discussed here previously and relevant blog posts below talks about them:

Paging and Available Memory for Operating sysem, what you need to know? 

SQL Server Memory - what's your method on Configuration and Troubleshooting Issues

Using PERFMON to get memory counters, what you need to check?

By default, SQL Server changes its memory requirements dynamically, on the basis of available system resources. If SQL Server needs more memory, it queries the operating system to determine whether free physical memory is available and uses the available memory. If SQL Server does not need the memory currently allocated to it, it releases the memory to the operating system. These are few common memory issues that occurs and need to troubleshoot them, page file is more important as it links with RAM to prevent excessive paging, the aim should not be to try to prevent paging activity completely.  In this case page faults will add more fuel to the problem, soft and hard page faults occurs on the server. A page fault occurs when a process requests a page in memory and the system cannot find the page at the requested location.  If the requested page is actually elsewhere in memory, then the fault is a soft page fault.  If the page has to be retrieved from the disk, then a hard fault occurs.  Most systems can handle soft page faults with no issues. 

However, if there are lots of hard page faults you may experience delays.  The additional disk I/O resulting from constantly paging to disk can interfere with applications that are trying to access data stored on the same disk as the page file.  Although high page faults on a system is a fairly straightforward issue, it requires some extensive data gathering and analysis in SYSMON, the important counters are Memory: pages/sec, Page Reads /sec & Available Bytes and let us see each of them :

If the Pages / sec multiplied by 4,000 (the 4k page size) is greater than 70% of the total number of Logical Disk Bytes / sec to the disk(s) where the page file is located on a consistent basis then you should investigate.  Then for Page Reads/sec which should be for for sustained values, so if the value is consistently greater than 50% of the total number of Logical Disk operations to the disk where the page file resides, then there is an inordinate amount of paging taking place to resolve hard faults. Then it comes to Available Bytes and when you see if it falls below 5 % of RAM that is installed then you should consider the type of service & application running at that time.

Such issues with paging talks more to the disk where the changes are written to the disk, that there will be page write operations occurring.  As the value of Available Bytes decreases, the number of hard Page Faults will normally increase.  The total number of Pages /sec that can be sustained by the system is a function of the disk bandwidth.  This does however mean that there is no simple number to determine whether or not the disks are saturated.  Instead you have to identify how much of the overall disk traffic is being caused by paging activity.

So whenever you see any memory issues then keep an eye on above SYSMON counters and monitoring should get you better information.

This is a very tricky question and hard to stick to 1 or few solutions as answer, it depends!

• Memory & I/O related:

SQL Server configuration disk layout - best practices from field

How SQL Server manages when a database is created in terms of I/O and disk usage?

Gauge the throughput of disk subsystem

SQL Server Clustering get pre-installation information and choose the best practices for your environment

I/O what I want to know - a newbie question

SQL Server 2005 memory configuration gotchas

SQL Server Memory - what's your method on Configuration and Troubleshooting Issues

• For Database maintenance related:

Index fragmentation and DBCC SHOWCONTIG in SQL Server 2005

SQL Server 2005 & 2000 - Index optimization best practices

More on Database Mirroring performance and index maintenance

Fragmentation on the database - close friend of a DBA

Memory - an important aspect of system performance within a RDBMS platform, not specific to a database product or application.

Coming to Microsoft related products such as Windows Server and SQL Server so on, various resources available on web such as MSDN blogs, Books Online and articles, frequently viewed concept for memory is such as /3GB switch and optimum memory settings. In any case the performance will be sufferred if there is a memory shortage, adding fule to the situation with insufficient resources. So to dig further into this issue always consider 2 aspects of troubleshooting, available physical memory and usage of available virtual memory by any process on the server. Taking the first one into consideration say when a system has little RAM (physical) available the system will switch to get resources from the virtual memory manager with an increased workload to give recently accessed virtual memory pages for that process, this is where you see lots of paging activity to the disk on the server. IN this case when you get statistics with SYSMON (PERFMON) the behaviour can be retrieved that will confirm the downtrend of application availability on the resources. The second one talked about virtual memory occupation, which will lead to memory leak throwing lots of exception errors & warnings having another load to paging until the system excperience the shortage, completely.

So how you can take the configuration and troubleshooting of such an important aspect, take this into SQL Server coding aspect within the usage of cursors or retrieving large result set with queries that will occupy the memory location and will be removed only when new request for physical memory addresses, this also because of the manner in which virtual memory address space is mapped to physical memory on demand. The slow performance investigation should continue from memory usage by identifying whether excessive paging is occurring or not. However, remember that excessive paging may occur even if there is plenty of available memory - for example if an application is leaking memory.  As referred above  it is important to have a baseline of your system's performance and SQLMemoryConfiguration_Gotchas to compare to any new performance statistics that you gather.  So the next question will arise what kind of PERFMON counters you should consider in gathering such data, that we talk as follows (typical to Windows Server setup):

(These are gathered from the web resources such as Technet blogs & SQL Server performance website websites and not to mention my own experience)

PERFMON Counter - Memory:

• Pages / sec - important to identify the rate at which pages are read from disk to resolve hard page faults. This means the disk usage will be very high and when a fault is occurred then the system tries to read multiple contiguous pages into memory to maximize the benefit of the read operation. In this case make sure to capture the 'Pages Input/Sec' & 'Page Reads/Sec' counters too that will give better idea on average number of pages read into memory during this operation.

Bear in mind if the disk hardware is not efficient then you will see spikes even for smaller operations, in general on a SQL Server installation during the backup operations (such as transaction log & database) you will observe a spike which is a common behaviour and better RAID configuration will do much better. A reference on relevant disk blog posts here:

Utilizing RAID is beneficial to both the security of your data and the performance of your shop

Is RAID5 better for performance when SQL Server Clustering and SAN is involved?

Best way to deploy SQL Server 2005 with SAN

How to gain Disk related performance with few simple steps

What's your practice on Disk Defragmentation methods - specific to data file and indexes drives?

Determining the required capacity of an I/O subsystem before deploying the database

• Pool Nonpaged Bytes - as referred they show the values in bytes that relates to area of system memory (physical) by the operating system for objects (threads) that cannot be written to disk. The life of these processes will be in memory only as long as they are allocated. So if you see the value more than 85% (constantly) then refer to this NonPaged-Depletion-issue blog post.

•  %Committed Bytes in Use - is the space that has been reserved in paging file, to be written to disk. AskPerf team given much insight on this information here - "What is the Page File for anyway?".

• Available Bytes - this is required to calculate the immediate availability of memory allocation to a process and make sure to monitor this constantly, as any value lower than 10% of installed memory (physical) then it indicates the system is sufferring a lot.

Based on above counters if you see a high spikes on page file then consider to resize the pagefile.sys on your system. Also one of the best practices is to keep the page file on a seperate drive to avoid any excessively busy & overall system performance may be impacted.  Memory leak is another point to raise here that means an application will show up as a gradual increase in the value of the Private Bytes counter listed above.  So going further to address the issues is to gather the data & perform  a baseline analysis as per above blog posts.

First and foremost solution is to add more RAM (physical) by checking the compatibility such as SQL Server editions & memory configuraiton (such as More than 3GB memory is not used even though boot.ini has these switches on /3GB /PAE /AWE.