SqlServer-QA.net

Here is an interesting question posted in the forums here asking about: Lots of time I face the problem with dynamic sql that I cant get the temporary table with data created through dynamic sql. for instance: SET NOCOUNT ONDROP TABLE EmployeeCREATE TABLE Employee(EmpId INT, EmpName VARCHAR(50), Sal    DECIMAL(13,2))INSERT INTO ...

Temporary database tempdb is created whenever the Server is restarted. So you can know when Server was restarted by knowing the creation date of the tempdb EXEC sp_helpdb tempdb select crdate  from master..sysdatabaseswhere name='tempdb'

I have submitted the feedback: ''Specify SQL Server services restart message when TEMPDB system database is moved to new path'' on Microsoft Connect site to get a correct execution message, for instance when you perform the TEMPDB data & log file ...

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

Configuration of server such as disk and memory is an important factor for SQL Server Performance, there is all about questions within newsgroups and forums that how do I setup the disk layout for a SQL Server database (say, 2005 version).  Not only this there will be more questions asked related to general guidelines or any other setup ...

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

Usage of Temporary tables or variables is a common scenario in SQL Server world, since SQL Server 2005 usage of TEMPDB references to the blog posts here: http://sqlserver-qa.net/blogs/tools/archive/2007/04/05/sql-server-index-optimization-best-practices.aspx, ...

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

A new Developer within our testing environment asked me this question to see whether they can use DETACH/ATTACH method than using ALTER database method. For the user databases it is available such method of DETACH/ATTACH method when moving the databases, but for system database it is not possible as they are in exclusive use for sqlserve.exe ...

Recently we have seen a negative performance for a query that is running against a 500GB size of database, where AUTO UPDATE STATISTICS is enabled and we perform weekly reindexing on this database.  In addition to this I perform intermittent update statistics against set of tables that has frequently update/deletes. As usual I have ...