Microsoft SQL Server

use master go if exists ( select * from tempdb.dbo.sysobjects o    where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_FILE_INFO' )) drop table #DB_FILE_INFO go if exists ( select * from tempdb.dbo.sysobjects o    where o.xtype in ('U') and o.id = object_id( N'tempdb..#DB_INFO' )) drop table #DB_INFO go set nocount on go create table #DB_FILE_INFO ( [ID] int not null identity (1, 1) primary key  clustered , [DATABASE_NAME] sysname not null , [FILEGROUP_TYPE] nvarchar(4) not null , [FILEGROUP_ID] smallint not null , [FILEGROUP] sysname not null , [FILEID] smallint not null , [FILENAME] sysname not null , [DISK] nvarchar(1) not null , [FILEPATH] nvarchar(260) not null , [MAX_FILE_SIZE] int null , [FILE_SIZE] int not null , [FILE_SIZE_USED] int not null , [FILE_SIZE_UNUSED] int not null , [DATA_SIZE] int not null , [DATA_SIZE_USED] int not null ,...

How to Configure static ports for SQL Server If a SQL Server instance is configured with a static port , SQL Server only listens on the specified static port. But it is important to delete all entries for dynamic ports otherwise SQL Server will listen on the static and dynamic ports.  Use Configure a Server to Listen on a Specific TCP Port (SQL Server Configuration Manager)  http://msdn.microsoft.com/en-us/library/ms177440.aspx Follow these details to the letter. Failure to follow the details – can leave a situation where the SQL Server is listening to the static and dynamic ports. Notes on configuring a static port for SQL Server 1)     If you want to use just a static port , clean up the entry for the dynamic port . If the dynamic port entry is not cleared , the setting  continues to be used. You can easily verify  by use the  sys.tcp_endpoints view . view source print ? 1. SELECT    ...

select      highest_cpu_queries.plan_handle,      highest_cpu_queries.total_worker_time,     q.dbid,     q.objectid,     q.number,     q.encrypted,     q.[text] from      (select top 50          qs.plan_handle,          qs.total_worker_time     from          sys.dm_exec_query_stats qs     order by qs.total_worker_time desc) as highest_cpu_queries     cross apply sys.dm_exec_sql_text(plan_handle) as q order by highest_cpu_queries.total_worker_time desc

/****************************************************************************** **    File: “GetTableSpaceUseage.sql” **    Name: Get Table Space Useage for a specific schema **    Auth: Robert C. Cain **    Date: 01/27/2008 ** **    Desc: Calls the sp_spaceused proc for each table in a schema and returns **        the Table Name, Number of Rows, and space used for each table. ** **    Called by: **     n/a – As needed ** **    Input Parameters: **     In the code check the value of @schemaname, if you need it for a **     schema other than dbo be sure to change it. ** **    Output Parameters: **     NA *******************************************************************************/ /*—————————————————————————*/ /* Drop the temp table if it's there from a previous run                 ...

SELECT r.session_id, s.TEXT, r.[status], r.blocking_session_id, r.cpu_time, r.total_elapsed_time FROM sys.dm_exec_requests r CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s 1) When an application reports a memory pressure issue – use the DMV to identify the current load and choose a session that may be causing a problem. view source print? 1. SELECT * FROM sys.dm_exec_sessions 2) The memory_usage column returns the number of pages used by this session. ORDER BY memory_usage DESC and analyse the session. It may not be the session causing performance hit , but a methodical approach is important. view source print? 1. SELECT * FROM sys.dm_exec_sessions ORDER BY memory_usage DESC 3) Create a query to join with sys.dm_exec_requests. The sys.dm_exec_requests DMV returns detailed information about executed requests in SQL Server. This query includes a CROSS APPLY to sys.dm_exec_sql_text. view source print? 01. select es.session_id,es.memory_usage,sql_h...