SqlServer-QA.net

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

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

Dynamic Management Views (DMV) are very useful to get server state information to monitor the status, but not always you have to still continue in monitoring using SYSMON & PROFILER that are available by default with SQL installation. The engine within SQL Server 2005 improves accuracy by using the high-resolution counters that are capable to ...

You may be aware using SP_WHO or SP_WHO2 statements you can get information about a session id and statement that is running since good olden days of SQL Server. Within SQL 2005 version you can take help of TSQL: SELECT  session_id, textFROM   sys.dm_exec_requests s1   CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2  ...

If you have a huge volume of table (say millions of rows) and a requirement to limit x number or sample of rows from a query every time you execute. To limit number of rows then you would think about [ select ... from (select top 100 * from X order by ... DESC) .... where ...] statement. But sometimes this will have degrade of ...

Is it possible to get statistics like how many times a stored procedure is called such as average execution time and how often that procedure is being called. Bascially it is possible to get provided that particular stored procedure is active and plan is available in the cache. Using a DMV sys.dm_exec_query_stats that will give the execution ...

There has been many instances of requests from the Developers to recompile a set of Stored Procedures on one of the mission critical databases, due to the performance issues the relevant SPs have been fine tuned for optimum performance. In order to take advantage of that immediate changes we have had a task of recompile 150+ odd stored procedures ...