When using OPENQUERY you will sometimes want to make calls to the same server you are working from. The most common reason for this is to query the output of a stored procedure into a temporary table. For that example, follow this link: (Insert Results of Stored Procedure Into Table) Before doing that you will [...]
Archive of entries posted on June 2009
Find Queries Performing Most Writes (Inserts)
SELECT TOP 10 ‘Procedure’ = qt.text ,DiskReads = qs.total_physical_reads — The worst reads, disk reads ,MemoryReads = qs.total_logical_reads –Logical Reads are memory reads ,Executions = qs.execution_count ,CPUTime = qs.total_worker_time ,DiskWaitAndCPUTime = qs.total_elapsed_time ,MemoryWrites = qs.max_logical_writes ,DateCached = qs.creation_time ,DatabaseName = DB_Name(qt.dbid) ,LastExecutionTime = qs.last_execution_time FROM sys.dm_exec_query_stats AS qs CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS qt ORDER BY [...]
Insert Stored Procedure Results Into Table
Retrieving the result set from a stored procedure has historically proven to be a difficult task. Fortunately with the advent of OPENROWSET, our life as developers has become much simpler. Using openrowset is very simple when we do not have procedures that take parameters, However when parameters become involved the game changes, however there is [...]
Insert Results of Query Into Table
There are a few ways to insert query results into another table. The most common way is to use the standard insert statement. This would insert query results into a table that already contains data. INSERT INTO dbo.Users ( Username ,FirstName ,LastName ,IsSuperUser ,AffiliateId ,Email ,DisplayName ,UpdatePassword ) SELECT Username ,FirstName ,LastName ,IsSuperUser ,AffiliateId ,Email [...]
Performance Counters
The following list of performance counters have been modified to include the most pertinent in identifying the bottleneck of your system. This list is for reference purposes. If needing to troubleshoot slowness read troubleshooting sql server slowness. Memory Object: – Memory Counter: – Available Mbytes Preferred Value: – > 2000MB Description: With 64 bit OS’s [...]
Rowcount for All Tables
SELECT SUM(rowcnt)
FROM sysindexes
WHERE indid < 2
Blocking Processes (lead blocker)
SELECT
spid
,status
,loginame = SUBSTRING(loginame, 1, 12)
,hostname = SUBSTRING(hostname, 1, 12)
,blk = CONVERT(char(3), blocked)
,open_tran
,dbname = SUBSTRING(DB_NAME(dbid),1,10)
,cmd
,waittype
,waittime
,last_batch
FROM master.dbo.sysprocesses
WHERE spid IN (SELECT blocked FROM master.dbo.sysprocesses)
AND blocked = 0
Turn On Deadlock Trace Flag
DBCC TRACEON (1204, -1)
Create Date Table
create table dates(
id int not null,
[date] as (yyyy+mm+dd),
yyyy char(4) not null,
mm char(2) not null,
Find Missing Table and Index Statistics
SELECT
Last_Updated = STATS_DATE(si.id, si.indid)
,TableName = object_name(si.id)
,Name = RTRIM(si.name)
,Size = DATALENGTH (si.statblob)
FROM
sysindexes si WITH (nolock)
WHERE OBJECTPROPERTY(si.id, N’IsUserTable’) = 1
–AND INDEXPROPERTY (si.id , si.name , ‘IsAutoStatistics’ ) = 0
order by last_updated, tablename
