Art of the DBA Rotating Header Image

sql scripts

Monday Scripts – Getting Partition Information

Apr 25th, 2011
by Mike Fal.
No comments yet

I’m going to follow up my post on partitioning with a query I wrote while implementing the partitions and cleaning up the empty ones. One of the issues I ran in to was clearly seeing each partition’s range and the number of rows within it. These two questions are answered in different places in the catalog views, sys.partition_range_values and sys.partitions. To build this out, I started with a nice little query from Derek Dieter, adding in a couple joins to get me to sys.partitions. Here’s the result!

select 
	t.name as TableName
	, ps.name as PartitionScheme
	, pf.name as PartitionFunction
	, p.partition_number
	, p.rows
	, case 
		when pf.boundary_value_on_right=1 then 'RIGHT' 
		else 'LEFT' 
	  end [range_type]
	, prv.value [boundary]
from sys.tables t
    join sys.indexes i on t.object_id = i.object_id
    join sys.partition_schemes ps on i.data_space_id = ps.data_space_id
    join sys.partition_functions pf on ps.function_id = pf.function_id
    join sys.partitions p on i.object_id = p.object_id and i.index_id = p.index_id
    join sys.partition_range_values prv on pf.function_id = prv.function_id and p.partition_number = prv.boundary_id
where i.index_id < 2  --So we're only looking at a clustered index or heap, which the table is partitioned on
order by p.partition_number

Monday Scripts – xp_logininfo

Apr 18th, 2011
by Mike Fal.
4 comments

How many times have you wished you could use SQL Server to query Active Directory and see who is in that group? This week’s script will allow you to do that, so if you have AD groups set up for logging into your server you can see exactly which logins have access.

xp_logininfo is an extended stored procedure that does just that. By passing a couple parameters, you can easily query any AD group that is a server principal. Wrap it up in a dynamic SQL loop, and you can quickly and easily audit your server’s security.

declare @winlogins table
(acct_name sysname,
acct_type varchar(10),
act_priv varchar(10),
login_name sysname,
perm_path sysname)

declare @group sysname

declare recscan cursor for
select name from sys.server_principals
where type = 'G' and name not like 'NT%'

open recscan
fetch next from recscan into @group

while @@FETCH_STATUS = 0
begin
insert into @winlogins
exec xp_logininfo @group,'members'
fetch next from recscan into @group
end
close recscan
deallocate recscan

select
r.name,
u.name,
u.type_desc,
wl.login_name,
wl.acct_type
from (select * from sys.server_principals where type = 'R') r
join sys.server_role_members rm on (r.principal_id = rm.role_principal_id)
join (select * from sys.server_principals where type != 'R') u on rm.member_principal_id = u.principal_id
left join @winlogins wl on u.name = wl.perm_path
order by login_name,r.principal_id,u.type_desc,u.name

Looking at the script, I first do a looped scan through sys.server_principals for all Windows groups (type ‘G’) and collect their AD group info. I store that in a table variable, then join it back to a select from server_principals again for a full listing of all my server permissions. Bam! Instant security report.

T-SQL Tuesday #17: Using APPLY

Apr 12th, 2011
by Mike Fal.
2 comments

I confess, I’m one of those guys who really didn’t know much about APPLY. Sure, I saw it in the occasional query I’d find on the ‘tubes, but I never really dug into what it was and why this piece of syntax was so important. Fortunately, this month’s T-SQL Tuesday has rolled around with an opportunity for me to do a little learning, so off to the Google-mobile!

Of course, you can easily find the MSDN article explaining apply, but there’s a much more detailed one I found on Simple Talk by Robert Sheldon. Conceptually, it’s pretty simple. APPLY, in both its CROSS APPLY and OUTER APPLY forms, allows you to call a table valued function for each row in your data set. In a lot of ways it’s like a join, but instead of two tables linked on a common field, you’re linking your query with a computed set of rows that you return with your function.

Now, I wish I could say I had some cool query to share, but tend to use APPLY in some simple ways to get query information. Here’s a query that makes use of the dynamic management functions dm_exec_query_plan and dm_exec_sql_text that gets a lot of work from me:

select
	er.session_id,
	d.name database_name,
	er.status,
	er.wait_type,
	eqp.query_plan,
	est.text
from sys.dm_exec_requests er
	join sys.databases d on (er.database_id = d.database_id)
	CROSS APPLY sys.dm_exec_query_plan(er.plan_handle) eqp
	CROSS APPLY sys.dm_exec_sql_text(er.sql_handle) est

Using this, you can get your text and your query plan for all queries currently running on your server. I like to use the query plans to troubleshoot long running queries, and the wait types help you identify the queries you might want to look at first.

Enjoy the other T-SQL Tuesday posts out there and huge thanks to Matt Velic(blog|twitter) for hosting this month!

Monday Scripts – Free Space by Drive

Apr 11th, 2011
by Mike Fal.
No comments yet

Here’s a quick script that will allow you to gather up your free space by drive on your server. You can use this to evaluate your space usage and address immediate concerns. It’s a straightforward query that uses dynamic SQL to query the files in each database’s sys.database_files view and the SpaceUsed property collected by the FILEPROPERTY function.

declare @return table
(drive_letter varchar(100),
 allocated_space decimal(15,2),
 used_space decimal(15,2),
 free_space decimal(15,2))

declare recscan cursor for
select name from sys.databases
where state = 0

declare @db varchar(100)
declare @sql varchar(max)

open recscan
fetch next from recscan into @db

while @@FETCH_STATUS = 0
begin
	set @sql = 	'use ['+@db+'] '+
				'SELECT	UPPER(SUBSTRING(filename,1,2)) ,'+
				'sum(a.Size)/128.0,'+
				'sum(FILEPROPERTY(a.Name,''SpaceUsed''))/128.0,'+
				'sum(a.Size-FILEPROPERTY(a.Name,''SpaceUsed''))/128.0'+
				'FROM ['+@db+'].dbo.sysfiles a ' +
				'GROUP BY SUBSTRING(filename,1,2)'
	insert @return
	exec(@sql)

	fetch next from recscan into @db
end

close recscan
deallocate recscan

select
	drive_letter,
	sum(allocated_space) total_alocated,
	sum(used_space) total_used,
	sum(free_space) total_free,
	sum(free_space)/sum(allocated_space) perc_free
from @return
group by drive_letter
order by drive_letter

The main trick of this and the reason why we can’t use sys.master_files is because FILEPROPERTY only provides data for the current database context. If you try to call FILEPROPERTY for a file not in your current database, it will return a NULL.  As a result, we need to dynamically switch database contexts and gather the data for all of our databases.

Monday Scripts – Making use of sp_spaceused

Mar 21st, 2011
by Mike Fal.
No comments yet

Happy Monday! One thing I’m planning to do with this blog is post useful scripts that I’ve written to hopefully make your life easier as a DBA. Nothing complex and scary, plus I hope to explain any of the particular tricks within the script so you can build upon them to fit your specific situation.

As DBA’s, we’re commonly called upon to manage the space on our servers. Usually this is just a matter of managing data files, the free space within those files, and the total space on the drives. However, there are times we need more detail. Today’s script is one I put together to use sp_spaceused, which is a stored procedure provided with SQL server to give you space information on specific objects (databases and tables, typically). It’s very handy for analyzing your tables for consumption.

The problem I ran into with the stored procedure is that it would just show one table at a time, and often I need to see all the tables in a database relative to each other. So this script is a simple cursor that executes dynamic SQL and gets all the space details for all your user tables for the current database context.

/*Create return table.*/
declare @return table
(name varchar(100),
rows bigint,
reserved varchar(30),
data varchar(30),
index_size varchar(30),
unused varchar(30))

/*Store current table.*/
declare @curr varchar(100)

/*Create cursor for all tables.*/
declare recscan cursor for
select name from sysobjects where type = 'U'

/*Use cursor to run sp_spaceused on all tables.*/
open recscan
fetch next from recscan into @curr
while @@fetch_status = 0
begin
insert into @return exec sp_spaceused @curr
fetch next from recscan into @curr
end

/*Clean up cursor.*/
close recscan
deallocate recscan

/*return data*/
select
name,
rows,
reserved,
data,
index_size,
unused
from @return
order by convert(bigint,left(reserved,len(reserved)-3)) desc

(You can download the script here.)

The only real gotchas to keep in mind is that the size values are returned as strings and the values are in kilobytes. If you need to modify them for readability/sorting, you’ll need a bit of string manipulation. See the ORDER BY clause for how I handle that.

You can also get this information from the DMVs in SQL Server. I plan on covering that in a later post. Until then, this script should take care of most of your needs and give you some additional insight into your databases.

Newer Posts →