A colleague of mine found this little gem of a query (sorry, don’t know the original author). It finds the 20 most expensive queries executed on a SQL server instance, sorted by execution time. CPU load is also returned by the query. Here it is: SELECT TOP 20 qs.sql_handle, qs.execution_count, qs.total_worker_time AS Total_CPU, total_CPU_inSeconds = –Converted from microseconds qs.total_worker_time/1000000, average_CPU_inSeconds = –Converted from microseconds (qs.total_worker_time/1000000)/ qs.execution_count, [...]

There are a few ways to retrieve the table sizes (both row count and size in bytes) in SQL server. The simplest is the sp_spaceused stored procedure which takes a table name as argument: EXEC sp_spaceused ‘table_name’ This is all fine, but what if you want to display info about a dozen or more tables? [...]

Here’s an easy, single line statement to check if a database exists in the current SQL-server instance: IF db_id(’db_name’) IS NOT NULL

Here’s a script to retrieve the name of the currently running SQL Server instance: SELECT @@SERVERNAME AS ‘ServerName’ Resultset: ServerName ————————— DEV-SQL (1 row(s) affected)

It can sometimes be handy to know in which database you are working. To get the name of the current database you can use a simple script: SELECT DB_NAME() AS DataBaseName Resultset: DatabaseName ———————— NorthWind (1 row(s) affected)

I’ve posted a snippet before that allows you to select a list of tables (or views, stored procedures, etc) from a SQL-Server database. It would be only logical to also have a snippet that allows you to select the columns in the table you’re interested in. The following piece of SQL code does just that. [...]

If you happen to forget your SQL Server password for ‘sa’ account, then here’s a simple query to help you reset it: ALTER LOGIN [sa] WITH DEFAULT_DATABASE=[master] GO USE [master] GO ALTER LOGIN [sa] WITH PASSWORD=N’MyNewPassword’ MUST_CHANGE GO In Case you remember your Old Password and want to change the ‘sa’ password, use this query: [...]

In a production environment we have a 64-bit SQL Server Std edition running on Windows Server 2003 x64 Enterprise. The box used to contain 16GB of RAM, but to improve performance we upgraded to 64GB. As suggested by Books Online, SQL Server needs permission to ‘lock pages in memory’ to take advantage of the available [...]

Sql statement to enumerate all user tables in a MS-SQL instance. SELECT a.[Name] as FunctionName FROM sysobjects a WHERE a.xtype = ‘U’ Meaning of sysobject.xtype values D : ? F : foreign keys FN : user defined functions P : stored procedures PK : primary keys S : system tables TR : triggers U : [...]