SqlServer-QA.net

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 ...

Have you ever wondered about flushing databases? Well not quite possible to flush it straight away and not a best practice too on the live server, so what it is about and you need to know about transactions state when they occur. Say when the changes occur in a database the changes are formed as transactions that are held in buffer pool ...

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 ...

Recently  I was involved in one of the Performance Audit exercise at a client's place where they complain about Server CPU is always HIGH and what they have observed is even the physical disk where TEMPDB is located has been used extensively, as they can see spikes from SYSMON counters. So whenever a performance problem arises (SQL ...

Using SP_WHO or SP_WHO2 is a common way to get list of executing processes currently on the server, within SQL Server 2005 you could take advantage of DMV that lists currently-executing statements. select r.session_id,status,substring(qt.text,r.statement_start_offset/2, (case when r.statement_end_offset = -1 then ...

As a programmer interacting with SQL Server's cache is not often needed, but when you do need to determine what is going on with the cache, or you simply need to flush the execution plans or data pages to tune a query, you now have the means to do so. Below is the TSQL I have used to obtain buffers that are used within memory by object (SQL ...

Though it is not a best practice to perform a CREATE INDEX on large tables during the online hours where you have number of users accessing the metadata of SQL Server database. Within SQL Server 2005 Enterprise Edition you can perform ONLINE indexes operation, in this regard review the blogs by SQLQuery Processing team about offline ...

Parameterizing T-SQL queries are a well-known task such as database programming and best practice in some tasks. It allows query plan reuse and eliminates the need of recompilation for multiple invocations of the same query that simply has different parameter values. However, there are times when parameterized queries might perform poorly, because ...

How can you calculate size of  an index in a database whenever a capacity planning exercise if performed? By default the size allocated for each user objects depends on the user application and on the amount of space that is created by the application for user-defined tables, global and local temporary tables, indexes, and table variables. ...

Continuation to the BlogPost on issuing or configuring SQL Servers to use certificates,  I have found useful reference on Technet blogs - Mr. Cert’s side of the story.