Make DBA life easier with powershell - Get File size and percentage of space used

As a SQL dba, we all manage disk spaces. Whether it is associated with data drive, log drive or even TempDB drive.

In one particular scenario, it can be bit tricky or rather I would say bit time consuming to get the correct insight on what is taking most spaces in your volume.

This scenario is where you are hosting several databases and their files on the same drive or mount points and each database files are managed in different file group and folders.

One way to know is by manually logging on to the SSMS and actually sees the size of each database files and location.

But the way, I am going to suggest you here is to use to our great PowerShell script. All you have to do is simply remote desktop and open/execute PowerShell script.

For example: to get all the files under c:\temp location and their space usage (in %), you can execute below script:

$path = "c:\temp"
$totalfilesize = [int]((Get-ChildItem $path | Measure-Object -Sum Length).Sum / 1kb)
Get-ChildItem $path  | Select-Object Name, CreationTime,  @{Name="kb";Expression={$_.Length / 1KB}}, `
@{Name="Percent";Expression={(($_.Length / 1KB)/$totalfilesize )*100}}| sort Percent -descending;

This is very handy especially when you are managing lot of low disk space alerts.

SQL Server audit and Lock request time out period exceeded. (.Net SqlClient Data Provider) error

Have you ever noticed "PREEMPTIVE_XE_CALLBACKEXECUTE" wait type on one of your SPID which is not letting you perform any operation at SQL Server instance???

I have seen this wait type  when using SQL Server auditing and it seems that transaction is stuck and blocking meta deta. The exclusive lock was placed on sysclsobj in master database

If you try to see corresponding OS thread(KPID), you would notice that thread  actually belongs to sql server agent task.

Below are some additional findings :

 SPID for server audit is blocking all other instance level operation
 Stopping and restarting sql server agent job does nothing
 "PREEMPTIVE_XE_CALLBACKEXECUTE" reflects that sql thread is waiting on OS level resource
 Eventually there is no wait resource

This is a bug in SQL Server 2008 R2 SP1 and is fixed in CU 6 : ): )
Puneet

Update statistics some key points!

As a DBA, we all know that update stats is one of the key, which let SQL optimizer to decide the best (best means fast :)) way to retrieve the query results.

Now we also know that the best practice is to not just let statistics get updated automatically but also to place some kind of manual plan.

Although, you can choose a best time to manually update the statistics on every server in your farm, but realistically, you don’t have control over the time of modification of data into the tables as its control by application/users/business.

So what you can do your best is to make sure that statistics are updated most of the time!

You can leverage "sys.dm_db_stats_properties" to monitor the stats on your objects, especially large objects.

For smaller to medium table, it’s not a big concern as auto stats with manual plans can take care of most of the situation but with Large size tables(millions of rows), it can be bit tricky!

I would not rely on default automatic update of statistics for large objects.

Tables with millions of rows mean, you have to wait longer before it reaches 20% of threshold value.

Thankfully, SQL Server 2008 R2 released trace flag 2371, which controls the update dynamically.

What this mean is larger the number of rows exposed to modification, smaller the threshold get set and quicker the automatic update can happen.

Having said all this, make sure you test this trace flag  properly for your application and then make a decision.

Memory architecture changed in SQL Server 2012

So far, as a DBA'S we were always  trained to configure memory on 64 bit OS and follow the things like lock pagesin memory, setting max and min memory etc

With the release of SQL Server 2012, some of the things need consideration as memory structure has changed a bit ( for good ofcourse :) )

In SQL Server 2012, now CLR and MPA are now part of the buffer pool itself.

What this means?????

This means requests for CLR allocation and multi-page allocation(MPA) will be handled by buffer pool and is not outside like it used to be in sql server 2008 R2 and previous versions.

Now when we are configuring max server and min server memory, we have to keep in mind that allocations of pages greater than 8KB, like that for CLR and MPA are also controlled by max and min settings.

So these settings need to be revised, whenever you are upgrading to sql server 2012.

sql server 2012  now will account for more memory allocations(even pages > 8kb) from buffer.

Also for the 32 bit SQL, AWE feature is deprecated.
 

Set based Windows function should remove the need of cursors completly

Today I was refreshing myself with the new Windows functions availaible in SQL Server 2012. It all started with back in windows server 2005, when we saw lot of usage of CTE,OVER(), RANK,DENSE_RANK etc to support lot of complex queries into simple code.

With SQL Server 2012, this list has extended with extended support over clause, FRAME, LEAD,LAG,FIRST_VALUE,LAST_VALUE,PERCENT_RANK,PERCENTILE_DISC etc

As a developer, you should leverage more of these set based functions and as a DBA, when reviewing code, make sure you see usage of these functions more :)

Effect of Transactional Replication on your Publisher database!

This is one of the other similar things, which you can only tell once you have performed some kind of test.

Have you ever wondered, If transactional replication has any overhead on your Production database? and if yes, then how much really?

As transactional replication relies on reading and writing transactional logs, The answer is "YES" :)

You will notice degrade in your write performance if you have transactional replication configured!

In a scenario, where you are hosting distribution database on the separate server, I have seen write performance going bad by even 30% !! Consider a scenario, where you are inserting 100 of thousands of rows as a part of your batch process.You will be better of pausing your replication, If you want to speed up this operation.

Where is my IO?

We all are aware of sys.dm_io_virtual_file_stats and how useful is this DMV, But to be honest I could not find even one place which could give me the right working script leveraging this DMV and tell me if currently IO is an issue or not!

Well it think wait is over.I tried to put something together and come up with this. Feel free to correct this, but this works for me.

Anything more than 25 ms(that’s a Microsoft threshold J), practically anything more than 80 - 100ms can be something which you can dig into.

This gives you the real time picture and not since last time sql server started.
You can change the WAITFOR DELAY number to increase the delta figure.

-- Calculates average stalls per read, per write, and per total input/output for each database file.

SELECT DB_NAME(database_id) AS [Database Name], file_id ,io_stall_read_ms, num_of_reads,

CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,

num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],

io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],

CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))

AS [avg_io_stall_ms]

into #iostats_first

FROM sys.dm_io_virtual_file_stats(null,null)

ORDER BY avg_io_stall_ms DESC;

WAITFOR DELAY '00:05:00';

-- Calculates average stalls per read, per write, and per total input/output for each database file.

SELECT DB_NAME(database_id) AS [Database Name], file_id ,io_stall_read_ms, num_of_reads,

CAST(io_stall_read_ms/(1.0 + num_of_reads) AS NUMERIC(10,1)) AS [avg_read_stall_ms],io_stall_write_ms,

num_of_writes,CAST(io_stall_write_ms/(1.0+num_of_writes) AS NUMERIC(10,1)) AS [avg_write_stall_ms],

io_stall_read_ms + io_stall_write_ms AS [io_stalls], num_of_reads + num_of_writes AS [total_io],

CAST((io_stall_read_ms + io_stall_write_ms)/(1.0 + num_of_reads + num_of_writes) AS NUMERIC(10,1))

AS [avg_io_stall_ms]

into #iostats_second

FROM sys.dm_io_virtual_file_stats(null,null)

ORDER BY avg_io_stall_ms DESC;

select f.[Database Name],f.file_id,

(s.io_stall_read_ms - f.io_stall_read_ms) as 'io_stall_read_ms',

case (s.io_stall_read_ms - f.io_stall_read_ms)

when 0 then 0

else

((s.io_stall_read_ms - f.io_stall_read_ms)/(s.num_of_reads - f.num_of_reads))

end as 'avg_read_stall_ms',

(s.io_stall_write_ms - f.io_stall_write_ms) as 'io_stall_write_ms',

case (s.io_stall_write_ms - f.io_stall_write_ms)

when 0 then 0

else

((s.io_stall_write_ms - f.io_stall_write_ms)/(s.num_of_writes - f.num_of_writes))

end  as 'avg_write_stall_ms',

(s.io_stalls - f.io_stalls) as 'io_stalls',

case (s.io_stalls - f.io_stalls)

when 0 then 0

else((s.io_stalls - f.io_stalls)/(s.total_io - f.total_io)) end as 'avg_io_stall_ms'

from #iostats_first f

join #iostats_second s on f.[Database Name] = s.[Database Name]

and f.file_id = s.file_id

drop table #iostats_first

drop table #iostats_second