I haven't been able to find a gui tool in the management console that will quickly tell you the compression status of an object. Below is a dirty script to find objects partitions that are compressed. It doesn't do much as far as taking to the next level such as returning index name and so forth, just for quick investigation
select
*
from
sys.partitions p
inner
join
sys.objects o
on o.object_id
= p.object_id
where data_compression != 0
As a DBA a common ticket item is "why is a store procedure running slow?" Usually when trying to figure out a slow proc, it will involve looking at the query plan. One method is to trace the sproc get an example where it's running slow, and execute in a test environment. Unfortunately query plans are heavily dependent on the conditions of the machine their running on and the environment of the data objects. Meaning is their fragmentation, are statistics off, has recompile just taken place ect, so going through motions laid out above might not give you the answer your looking for. Rather an easier way is to grab the actual plan being run on the box that's having the issue. This can easily be done using profiler and selecting the Event Performanceà Showplan xml. Doing this uses a fairly heavy monitoring criterion, and blindly applying without filtering will take you live box to its knees. In order to find procedure query plans in an efficient manner I like find the objectid of the procedure that I'm chasing, then use the criteria in "column filters " under ObjectID. Make sure the "Exclude rows that do not contain values" is check to avoid unneeded information gathering. In addition to the Showplan I'll also collect Stored Procedures àSp:Starting and Sp:Completed to give more detailed information such as amount of read, start and stop time, and also to see if the client timed out with a starting statement being issued without a completed statement.
Using sys.dm_db_index_usage_stats is a great DMV for looking up the usage of an index. It allows you to see how many scans, lookups, updates, ect, have occurred on the index, and therefore serves to give you a mid level view of what's happening with your structures. However sometimes you need to dig a little deeper into the details such as what stored procedures are actually working against these. To get at this information I like to leverage the sys.dm_exec_query_plan DMV, to search through the plan and look for the index in question. In the query below I join to the query_stats dmv, because I prefer to have a little more info about the query. The query below shows you all the plans where the index is being referenced. Once you pump the results out, you can save the plan with a sqlplan extension and open up in management studio. If you're using 2008 double clicking on the plan will automatically open the plan for you. From here it's simply a matter of looking for where the index is referenced.
select
object_name(objectid)
as CallingObject,
*
from
sys.dm_exec_query_stats qs
cross
apply sys.dm_exec_Query_plan(qs.plan_handle)
where
convert(nvarchar(max),query_plan)
like
'%[index Name]%'
If you're a DBA with more than a few months experience you've seen the following error "Exclusive access could not be obtained because the database is in use", when trying to restore a database. The message is self explanatory; you can't restore a database because someone else is currently using it. Under most circumstances this is a fairly easy fix by querying sysprocesses and killing any users in the database you're trying to restore. Unfortunately sometimes you'll have a cross database query that doesn't show up when looking for the culprit. For example if in DB_A you're running an update statement against DB_B, and you query sysprocesses or any other DMV you won't see anyone accessing DB_B, because the the transaction is actually running within DB_A. Below is a dirty script to get at this info, it could probably be done a few different ways and much cleaner, but will usually allow you to find the offending transactions.
Declare @DBNametoKillInput varchar(100)
Declare @DBName varchar(110)
--Set DB Name you want to kill
set @DBNametoKillInput =
'email'
--Generate a like expression
set @DBName =
'%'
+ @DBNametoKillInput +
'%'
print @dbname
select
object_name(st.objectid),
*
from
sys.dm_exec_requests r
cross
apply sys.dm_exec_sql_text(r.sql_HANDLE) st
WHERE st.text LIKE @dbname
The more common approach is in recompiling your queries. This is the most used tool in a DBA's toolbox. The issue here is it's caught too often after plenty of damage is done. Depending on the robustness of application and database monitoring a bad query can be running for hours or days. The workaround to this is to write your code by using the with recompile option. This will recompile a sproc on every execution. This type of approach works great if your sproc is called less than 4 times a minute, however for often called sprocs this can cause more harm than good. The reason is the recompile will often take more resources and time then if the plan is good, which should be the case 99% of the time. To address this issue I've written a script below.
Script Explained
This leverages sys.dm_exec_query_stats and sys.dm_exec_sql_text DMV's, looking at the maximum amount of reads performed. If this threshold is hit it runs a recompile then alerts the DBA's that a recompile has occurred. Using the reads is specific to an issue we've seen, but worker time could be a better canidate for a bad query. We've scheduled it to run every 15 min through sql agent. One last thing is that it uses a global temp table over a local temp table, this deals with some issues on context switching of the mail sproc http://www.dbafieldnotes.com/2009/11/sql-server-using-spsenddbmail-and-temp.html
--Check Existance
if
exists(select *From tempdb.sys.tables where name = '##QStats')
begin
drop
table ##QStats
end
--Declare variables
Declare @objectname varchar(100)
Declare @ReadThreshold int
Declare @Reads int
Declare @Sub varchar(255)
Declare @bod varchar(500)
Declare @QueryVar varchar(1000)
--Fill in specifics
set @objectname ='sprocname'
set @ReadThreshold = 20000
--Applied before used may not be needed but easier to code
set @Sub = 'Sproc Recompiled for ' + @objectname
set @bod = 'A recompiled occurred on '+ @objectname +' Because the amount of reads was '+ convert(varchar(10), @reads)+' while the threshold is set to ' + convert(varchar(10), @ReadThreshold) +
' other stats are attached in body '
--Some nice stats to send in email
set @QueryVar =
'select plan_generation_num, creation_time, last_execution_time,execution_count,total_worker_time/execution_count As AVGWorkerTimeInMicrosecs,total_logical_reads/execution_count As AVGreads,total_logical_writes/execution_count As AVGWrites,total_elapsed_time/execution_count as AvgDurationnMicrosecs,total_clr_time/execution_count as AvgCLR from ##QStats'
--Put data into temp table since it might be used again in email
select
* into ##QStats from sys.dm_exec_query_stats qs cross apply sys.dm_exec_sql_text(qs.sql_handle) where objectid = object_id(@objectname)
--Get reads and assign logical reads
select @Reads = max(max_logical_reads) from ##QStats
If @Reads > @ReadThreshold
Begin
--Run Recompile
exec sp_recompile @objectname
--Mail DBA's with info.
exec msdb.dbo.sp_send_dbMail 'DBMailProfile',
@recipients ='test@test.com',
@Subject =@sub,
@body = @bod,
@importance ='High',
@Query = @QueryVar,
@Attach_Query_result_as_file = 1
end
If you try to refrernce a temp table in use sp_send_dbmail in a script you'll notice can't reference the table. For example the below script will not work.
DECLARE @STMT VARCHAR(100)
SET @STMT = 'SELECT * FROM #TEMPTABLE'
SELECT TOP 10 * INTO #TEMPTABLE FROM SYS.SYSPROCESSES
exec msdb.dbo.sp_send_dbMail 'DBMail',
@recipients = 'test@test.com',
@Subject ='TEST',
@body ='TEST',
--@importance = 'High',
@Query = @STMT,
@Attach_Query_result_as_file = 1
The reason for this is the sp_send_dbmail doesn't execute in the same session, rather it will create its own spid to execute. It does this by issuing a "Execute as Login" under the account the sql server process is running under. Doing so does not allow you to access the local temp table from the previous session. An easy work around here is to use a global temp table which can be seen by all sessions. So the following statement will work.
DECLARE @STMT VARCHAR(100)
SET @STMT = 'SELECT * FROM ##TEMPTABLE'
SELECT TOP 10 * INTO ##TEMPTABLE FROM SYS.SYSPROCESSES
exec msdb.dbo.sp_send_dbMail 'DBMail',
@recipients = 'test@test.com',
@Subject = 'TEST',
@body = 'TEST',
--@importance = 'High',
@Query = @STMT,
@Attach_Query_result_as_file = 1
SSIS doesn't have a built in wait task, which has lead to many different innovative solutions that have come out of this. Here's is yet on more which involves exploiting the script task, and threading class. The solution is fairly strait forward; it simply puts the thread to sleep for a given amount of time in milliseconds. Where you need a wait put a script task into the package. Inside this task place the following line of code.
System.Threading.Thread.Sleep(x)
The "x" represents time in Milliseconds. A code snippet of how this would look something like this.
Public
Sub Main()
Dim wait As
Integer
wait = CInt(Dts.Variables("IntervalWaitInMS").Value.ToString)
System.Threading.Thread.Sleep(wait)
Dts.TaskResult = Dts.Results.Success
End
Sub
Some helpful advice here is that it uses variable above IntervalWaitInMS to assign this value, which would usually come from a user entered config. The problem here is this is in milliseconds, and this number can become quite large. If for example you wanted to have wait period of 1 hr, you'd have to enter a value of 3600000. Missing one 0 in your config for a value 360000 is the difference between 1 hr and six minutes. To make it a bit more user friendly when setting up your configs you might want have the users enter their value in minutes, and do the conversion within the script, such as below
Public
Sub Main()
Dim wait As
Integer
'Convert Min to Milliseconds
wait = CInt(Dts.Variables("IntervalWaitInMin").Value.ToString) * 60000
System.Threading.Thread.Sleep(wait)
'MsgBox(Dts.Variables("ValidationStatus").Value.ToString)
Dts.TaskResult = Dts.Results.Success
End
Sub
