|
|
|
|
-
-
I'll be presenting two sessions this Saturday (January 30, 2010) in Richmond - Automate SQL Server Administration with PowerShell and Gather SQL Server Performance Data with PowerShell. The event takes place at ECPI College of Technology, 4305 Cox Rd, Glen Allen, VA. For more information please visit SQLSaturday.com or email us at sqlsaturday#30@sqlsaturday.com.
I hope to see you there.
Allen
|
-
The results are in, and I'm pleased, but not satisfied. I delivered a brand new session called Gather SQL Server Performance Data with PowerShell, where I discussed key performance counters you should monitor and why, then a PowerShell script to gather them and store them in a SQL Server database, then created a report which I loaded and ran from SQL Server Management Studio to keep an eye on performance trends.
So, what did my audience think? Here are the results (where a Very Poor rated a 1 and an Excellent rated a 5):
Overall
4.41
How would you rate the usefulness of the session information in your day-to-day environment?
4.23
How would you rate the Speaker's presentation skills?
4.46
How would you rate the Speaker's knowledge of the subject?
4.69
How would you rate the accuracy of the session title, description, and experience level to the actual session?
4.46
How would you rate the amount of time allocated to cover the topic/session?
4.31
How would you rate the quality of the presentation materials?
4.28
Interestingly, I did get one evaluation with all the questions rated Very Poor. I ask anyone filling out an evaluation to please put comments if a session is that bad, because without knowing what it was that made it so poor, we can't do much to correct the problem.
I enjoyed delivering the session, and hope to do so again at Tech Ed in New Orleans. Thanks to everyone who attended.
Allen
|
-
Last week I introduced the Service Broker Basics. Today I'd like to cover some of the "plumbing" - the components that allow communication between different servers running Service Broker.
Endpoints. There needs to be a channel for the communications coming in and out of the server, and in the IP world that channel exists in the form of a port. You define the port to be used by defining an Endpoint in the master database.
CREATE ENDPOINT IntEndpoint
STATE = STARTED
AS TCP ( LISTENER_PORT = 4022 )
FOR SERVICE_BROKER (AUTHENTICATION = WINDOWS );
GO
Routes. To get from one place to another Service Broker routes need to be defined. You'll need a route to the remote server defined in the database where your Service Broker application is running, and also one to the local server, and the latter needs to be defined in the msdb database. Defining a remote destination in your application database places the route information in sys.routes, but Service Broker always looks in msdb.sys.routes for any incoming messages to determine where they go.
USE AdventureWorks
GO
CREATE ROUTE DMZRoute
AUTHORIZATION dbo
WITH
SERVICE_NAME = N'//DMZSite/Sync/IntService',
ADDRESS = N'TCP://SQLTBWS:4023'
GO
USE msdb;
GO
CREATE ROUTE IntRoute
AUTHORIZATION dbo
WITH
SERVICE_NAME = N'//IntSite/Sync/IntService',
ADDRESS = N'LOCAL'
GO
One thing I hadn't addressed in my last post was message security. Service Broker allows you to encrypt all messages, preventing network sniffers from discovering the data being sent. To enable this I created certificates at each site, and created a database user without a login to send and receive messages. Here's the code I used to create the local user:
USE AdventureWorks
GO
CREATE MASTER KEY
ENCRYPTION BY PASSWORD = N'<enter REALLY secure password string here>';
GO
CREATE USER IntUser WITHOUT LOGIN;
GO
CREATE CERTIFICATE IntCert
AUTHORIZATION IntUser
WITH SUBJECT = 'Int Certificate',
EXPIRY_DATE = N'12/31/2012';
BACKUP CERTIFICATE IntCert
TO FILE = N'E:\Certs\IntCert.cer';
GO
I did the same thing at the destination site (called DMZSite), and to allow the DMZUser to send messages to my site I'll create a local user from the certificate created at that site.
CREATE USER DMZUser WITHOUT LOGIN;
CREATE CERTIFICATE DMZCert
AUTHORIZATION DMZUser
FROM FILE = N'E:\Certs\DMZCert.cer';
GO
Remote Service Binding. Once the users are established and secure, the last component required is the Remote Service Binding. This binds a remote Service Broker service to our local one, defining the security credentials to be used in the conversations.
CREATE REMOTE SERVICE BINDING [DMZBinding]
AUTHORIZATION dbo
TO SERVICE N'//DMZSite/Sync/IntService'
WITH USER = [DMZUser]
GO
Finally, we'll grant the SEND permission to the DMZUser to allow the remote service to send messages to our site.
GRANT SEND
ON SERVICE::[//IntSite/Sync/IntService]
TO DMZUser;
GO
As I mentioned before, this set of objects make up the "plumbing" that allow separate instances or servers to communicate with each other. In my next post we'll talk about the automated activation process and walk through the steps of message handling.
Allen
|
-
I'm currently implementing a Service Broker solution at a client site, and it's been an interesting challenge, because there's not a lot of information out there to help guide you through the process. Here I'd like to walk you through the basics.
Message Types. Service Broker sends messages asynchronously from one database to another. You can set it up to send messages between databases on a single server, or between SQL Server instances on a Windows server, or between different physical servers, whether or not they're in the same domain. Essentially Service Broker works at the database level, the rest is handled through routing, which I'll address in another post.
The important thing to remember is that Service Broker sends and receives messages, and then your applications (or stored procedures) handle those messages in some way. It handles them asynchronously, so the sending side doesn't have to wait for the receiving side to acknowledge the message, and it handles them sequentially, so the messages will always arrive in the order in which they've been sent.
Many of the examples you'll see use message types like "REQUESTMESSAGE" and "REPLYMESSAGE". To me this is a disservice, because it doesn't help you see the different ways you can use Service Broker to solve your business problems. At my client site the message types indicate the content of the message, so the receiving side can use the type to determine the action to take when the message is received. Service Broker has a built-in acknowledgement process, so you don't need to specifically acknowledge a message, unless the application needs it. As long as the communication channels are open, the message will be delivered.
CREATE MESSAGE TYPE [//AWSync/Sync/HumanResourcesEmployee]
AUTHORIZATION dbo
VALIDATION = WELL_FORMED_XML
GO
Contracts. Once you've defined the types of messages that can be sent, you need to define how they'll be delivered. Contracts define what message types are allowed to be sent, and in which direction. This means that Service Broker is secure in that it won't process any messages types not defined in a contract, so rogue processes that attempt to try a type of SQL Injection attack against Service Broker will fail.
CREATE CONTRACT [//AWSync/Sync/IntContract]
AUTHORIZATION dbo
( [//AWSync/Sync/HumanResourcesEmployee] SENT BY ANY,
[//AWSync/Sync/PersonContact] SENT BY ANY,
[//AWSync/Sync/PurchasingVendor] SENT BY ANY )
GO
Queues. Once the contract is defined, you can define the queue on which the messages are sent and received. The queue also defines (if you want) an automated process that will handle the messages it receives. In your Transact-SQL code you retrieve messages from the queue in the same way you read data from a table - in fact, the queue behaves just like a table in your database.
CREATE QUEUE IntQueue
WITH
STATUS = ON,
RETENTION = OFF
GO
Services. The service is the glue which assigns the contract to the queue. It performs the work of actually sending the messages on the queue to their destination and receiving the messages coming from other senders.
CREATE SERVICE [//IntSite/Sync/IntService]
AUTHORIZATION IntUser
ON QUEUE IntQueue
([//AWSync/Sync/IntContract])
GO
Conversations. In its simplest form, the last thing we need is to send the message. We do that via a conversation, which is referred to in Service Broker as a DIALOG CONVERSATION or simply a DIALOG. You specify the source and destination service name, and a conversation handle (a GUID) is returned, then you SEND ON CONVERSATION using that conversation handle. The message body is usually in an XML form, and for security purposes should be encrypted.
BEGIN DIALOG @InitDlgHandle
FROM SERVICE [//IntSite/Sync/IntService]
TO SERVICE N'//ExtSite/Sync/IntService'
ON CONTRACT [//AWSync/Sync/IntContract]
WITH
ENCRYPTION = ON;
SEND ON CONVERSATION @InitDlgHandle
MESSAGE TYPE [//AWSync/Sync/HumanResourcesEmployee]
(@ChangeMsg);
Finally, you need to be able to receive the messages. Like I mentioned earlier, reading from a queue is like reading from a table, but there are some additional features in Transact-SQL to facilitate message handling. Specifically, there's a special form of the WAITFOR command which will wait for either the arrival of a message, or timeout after a specified number of milliseconds.
WAITFOR (
RECEIVE TOP(1)
@ch = conversation_handle,
@service_name = service_name,
@service_contract_name = service_contract_name,
@messagetypename = message_type_name,
@messagebody = CAST(message_body AS XML)
FROM ExtQueue
), TIMEOUT 60000
With these components you can set up messaging within a single instance of SQL Server. In my next post I'll discuss the additional plumbing required to communicate between separate instances.
Allen
|
-
Well, it's been an interesting year. I got to teach four different brand new SQL Server courses, and teach classes in four different countries on two continents. Two books were published with chapters I wrote. Microsoft published a whitepaper I wrote. And finally I've started a new business and it's time consuming but so far successful.
I'd like to thank everyone who helped make this all possible, specifically Kieth Nicholson, Paul Nielsen, Kalen Delaney, Adam Machanic, Dan Jones, Don Vilen, Tony Davis and Shawn Upchurch. There are so many in this community who's friendship and support have helped in so many ways.
Here's to everyone who's active in the SQL Server Community and to a healthy, happy new year!
Allen
|
-
As a DBA, one of the things I struggled with was gathering all my important system information in one place, so I could see easily where my problems might be. Gathering perfmon data, combining it with trace data, adding in disk space information - it was hard to do this automatically.
This is probably why I like PowerShell so much. I can do this from a single script and don't have to try to merge data later.
The perfmon data gathering was always a problem for me, trying to export the log files into something useful. I've found that .NET has a namespace specifically for that - System.Diagnostics.PerformanceCounter. By creating (instantiating) objects of the PerformanceCounter type, I can collect the same perfmon counter data that perfmon, and I can control where that data goes. Here's the PowerShell code to capture some key counters:
$ppt = New-Object System.Diagnostics.PerformanceCounter
$ppt.CategoryName = 'Processor'
$ppt.CounterName = '% Processor Time'
$ppt.InstanceName = '_Total'
$pptv = $ppt.NextValue()
$mab = New-Object System.Diagnostics.PerformanceCounter
$mab.CategoryName = 'Memory'
$mab.CounterName = 'Available MBytes'
$pql = New-Object System.Diagnostics.PerformanceCounter
$pql.CategoryName = 'System'
$pql.CounterName = 'Processor Queue Length'
$bch = New-Object System.Diagnostics.PerformanceCounter
$bch.CategoryName = 'SQLServer:Buffer Manager'
$bch.CounterName = 'Buffer cache hit ratio'
$brs = New-Object System.Diagnostics.PerformanceCounter
$brs.CategoryName = 'SQLServer:SQL Statistics'
$brs.CounterName = 'Batch Requests/sec'
So, in these few lines we're setting up the collection for Percent Processor Time, Available Megabytes of Memory, Processor Queue Length, Buffer Cache Hit Ratio and Batch Requests/sec. Not a bad start to building our performance baseline. Notice that I called the NextValue method for the Percent Processor Time counter. I did this because this counter needs to be "seeded" - the first returned value is always zero.
Next we need to have a place to put the values. I like the fact that PowerShell uses objects, and learned that youc an create your own objects, with whatever properties you'd like. COOL!
$perf = new-object object
Now I have an object called $perf with no properties. I need to add the properties corresponding to the counters I'm collecting, so I'll use the Add-Member cmdlet to do this. (Note that piping the $perf object into the add-member cmdlet causes the object to become the value passed via the -InputObject parameter to add-member. The pipeline doesn't really go right-to-left here.)
In my case I'm setting up each additional properties as NoteProperties, and will assign values to them. I could have also used the ServerProperty parameter and used a scriptblock, but every time the object is referenced it would run the script block, and I wanted the results to be static in time.
$perf | add-member NoteProperty Server 'MyServer'
$perf | add-member NoteProperty Date get-date
$perf | add-member NoteProperty PctProc $ppt.NextValue()
$perf | add-member NoteProperty Memory $mab.NextValue()
$perf | add-member NoteProperty ProcQueLn $pql.NextValue()
$perf | add-member NoteProperty BufCchHit $bch.NextValue()
$perf | add-member NoteProperty BatRecSec $brs.NextValue()
I do need a place to put the object once it's populated, so I'll create an empty array like this:
$sysperf = @()
Then, at the end of each iteration I add the object to the array.
$sysperf += $perf
Now I can just loop through this last bit of code every 15 seconds or so to get a feel for how the system is performing, like this:
Start-Sleep -s 15
Finally, when I've got the information I want, I can use the export-csv cmdlet to load the data into a CSV file.
$sysperf | export-csv -noTypeInformation sysperf.csv
I could also use the Invoke-SQLCMD cmdlet to load it directly to SQL Server if I chose, but this is easier at a client site.
At the PASS Community Summit in Seattle next month I'll be demonstrating the full script using this code. Hope to see you there.
Allen
|
-
I got tagged by my buddy Michael Coles, aka Sergeant SQL to talk about how I "became" a geek. (I use quotes because I've really always been a geek.)
Back in the 9th grade (1968 for those of you counting) my math teacher asked if there was anyone in the class interested in learning how to program computers. My friend Bronis Vidugiris and I raised our hands, and I learned BASIC from a GE manual, then learned how to use a teletype machine with a paper tape reader/punch on one side and a 300 baud acoustic coupler on the other. We'd punch our programs onto paper tape, then dial the "mainframe", load and debug our programs, then punch the updated program back to a fresh tape when it was done.
After my attempt at being a theatre major failed I went to college and got a degree in Data Processing, while working as a nighttime operator at American Greetings, running 2nd Generation (transistor-based) NCR 315 Rod Memory Computers. (This is a long story best suited for an evening with my fermented beverage of choice.) That lead to years of being a programmer on mainframes using punched cards and COBOL, then working my way towards smaller systems - minis, then micros and to programming C on Unix based machines.
One of my engagements while a contract programmer introduced me to relational databases (Oracle 6, at the time), and the next one required Sybase. The latter company became my full time employer, and they switched from Sybase to Microsoft SQL Server in 1995, and I've been working with SQL Server ever since, in either a DBA or developer (or both) role.
(My personal life was not immune from computers, as I bought the TI-99/4A when it came out, then the Amiga 1000, and on to the Amiga 2000.)
So, to now continue the stories coming, I'd like to tag Aaron Bertrand and Paul Nielsen.
See you at PASS!
Allen
|
-
I've had to rework some logic for gathering database information in my serverstatus.ps1 script, because SMO doesn't exactly do things the way one would think. The result is that the performance is unacceptable when dealing with multi-terabyte databases.
In gathering the actual data, then, I resorted to good old fashioned T-SQL via ADO.NET. (I have a followup script to load the collected data using the Invoke-SQLCMD cmdlet, but I don't want to force my clients to load the SQL 2008 tools to run my data collection script.) To gather the information I'd gotten previously using the SMO Databases collection I have to do things in multiple steps, and I use temporary tables to get there. (Yes, a stored procedure would be a better choice here, but I don't want to install anything on client systems.)
The first step is to gather everything about the databases using the following T-SQL command through ADO.NET via PowerShell:
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$inst;Integrated Security=SSPI;Initial Catalog=master");
$cn.Open()
$q = "SELECT dtb.database_id as [db_id],"
$q = $q + " dtb.name as [Name],"
$q = $q + " dtb.collation_name AS [Collation],"
$q = $q + " CASE dtb.compatibility_level"
$q = $q + " when 80 then 'Version80'"
$q = $q + " when 90 then 'Version90'"
$q = $q + " when 100 then 'Version100'"
$q = $q + " END AS [CompatibilityLevel],"
$q = $q + " CASE when dtb.is_auto_shrink_on = 0 then 'False'"
$q = $q + " else 'True'"
$q = $q + " END AS [AutoShrink],"
$q = $q + " CASE dtb.recovery_model"
$q = $q + " when 1 then 'Full'"
$q = $q + " when 2 then 'BulkLogged'"
$q = $q + " when 3 then 'Simple'"
$q = $q + " END AS [RecoveryModel],"
$q = $q + " CAST(0 AS float) AS [Size],"
$q = $q + " CAST(0 AS float) AS [SpaceAvailable]"
$q = $q + " into tempdb.dbo.tmpdbs"
$q = $q + " FROM master.sys.databases AS dtb"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$dr = $cmd.ExecuteNonQuery()
$cn.Close()
This creates a persistent table called tmpdbs in tempdb. It uses the ExecuteNonQuery method of the SqlCommand object, allowing me to do work without expecting results back. I create a persistent tempdb table because I'm closing my connection after the query completes. (Database Best Practice: Open connections late, close them early.)
Next, while iterating through the databases gathering the file size details I use the following commands to gather and update the rows in the persistent tempdb table:
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$inst;Integrated Security=SSPI;Initial Catalog=$dbname");
$cn.Open()
$q = "create table #tmpspc"
$q = $q + " ("
$q = $q + " Fileid int,"
$q = $q + " FileGroup int,"
$q = $q + " TotalExtents int,"
$q = $q + " UsedExtents int,"
$q = $q + " Name sysname,"
$q = $q + " FileName nchar(520)"
$q = $q + " ) "
$q = $q + "insert #tmpspc EXEC ('dbcc showfilestats')"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$dr = $cmd.ExecuteNonQuery()
$q = " select (SUM(CAST(TotalExtents as float)) * 64) / 1024.0 AS [Size],"
$q = $q + " ((SUM(CAST(TotalExtents as float)) - "
$q = $q + " SUM(CAST(UsedExtents as float))) * 64) / 1024.0 AS [SpaceAvailable]"
$q = $q + " into #tmpdbz"
$q = $q + " from #tmpspc"
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$dr = $cmd.ExecuteNonQuery()
$q = " UPDATE d"
$q = $q + " SET d.[Size] = z.[Size]"
$q = $q + " ,d.[SpaceAvailable] = z.[SpaceAvailable]"
$q = $q + " from #tmpdbz z, tempdb.dbo.tmpdbs d"
$q = $q + " WHERE d.db_id = " + [string]$db.ID + ""
$cmd = new-object "System.Data.SqlClient.SqlCommand" ($q, $cn)
$dr = $cmd.ExecuteNonQuery()
$cn.Close()
Lastly, I use a SqlDataAdapter and a DataTable object to gather the results and send them to the output CSV file.
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$inst;Integrated Security=SSPI;Initial Catalog=master");
$cn.Open()
$q = "select Name, Collation, CompatibilityLevel, AutoShrink, RecoveryModel, Size, SpaceAvailable"
$q = $q + " FROM tempdb.dbo.tmpdbs
"
$q = $q + " drop table tempdb.dbo.tmpdbs"
$dbsf = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$dbsf.SelectCommand.CommandTimeout = 0
$dbsf.Fill($dbf)
$dtDatabase = new-object "System.Data.DataTable" "dtDatabaseData"
$dtDatabase = $dbf.Tables[0]
$outnm = ".\" + $svr + "\" + $instnm + "_GEN_Databases.csv"
$dtDatabase | select Name, Collation, CompatibilityLevel, AutoShrink,
RecoveryModel, Size, SpaceAvailable | export-csv -path $outnm -noType
$cn.Close()
As with anything, there's more than one way to solve a problem, but I found this solution performs well even with very large databases, and avoids the insane number of round-trips to the server that the SMO object makes.
P.S. Thanks to Philip Bondi for asking about this.
Allen
|
-
One of the cool features of the SQL Server PowerShell snapins is the the ability to browse SQL Server as though it was a file system. While Management Studio has the nice graphical tree structure, sometimes I find it more productive to walk up and down the tree in a text environment. One thing I really like to see is the DDL script for the objects of interest because that script tells me a lot in a concise manner.
(Either run SQLPS.exe from SQL Server 2008, or use the script in Michiel Wories' blog post here to load the SQL Server PowerShell snapins.)
So, how do I see the script of the objects I'm looking at? It's easy, once you know.
Let's say we're browsing the AdventureWorks database and I want to look at the tables.
cd SQLServer:\SQL\SQLTBWS\INST01\Databases\AdventureWorks\Tables
ls
(Note that 'cd' is an alias for Set-Location and 'ls' is an alias for Get-ChildItem.)
These commands return the list of tables in AdventureWorks. If I change to the Person.Address "directory" I can easily script the table DDL like this:
cd Person.Address
$pa = get-item .
$pa.Script()
If I then change to the indexes directory and list the items there, I see four indexes. I can easily script one using these commands:
$ixp = get-item IX_Address_StateProvinceID
$ixp.Script()
If I want to look at some stored procedure code, I can do that just as easily.
cd ../../../StoredProcedures
$spel = get-item HumanResources.uspUpdateEmployeeLogin
$spel.Script()
Of course, you can also pipe these objects to the out-file cmdlet and save the script to text files if you'd like. The important thing is that you can browse your system easily and find useful information to help you understand your current application architecture.
Allen
|
-
Yesterday I posted about how Transact-SQL was better suited than SMO for gathering details about database files for a database with very large files. Today I'd like to point out that creating the database I used to test that fix was far easier with PowerShell and SMO than any other method.
SQL Server, and Transact-SQL specifically, are very good at set-based activity, and that's a good thing. Relational databases are based on a set model. To create all the files I needed for my test I needed an iterative process, and while Transact-SQL can do that, other languages are better at it.
Here's the script I used to add all the filegroups and files needed for my test. I needed over 380 filegroups to match what my client database looked like, and each filegroup had to have 32 files in it. Before running the script I created a small database (2MB data, 1MB log) called "TestLots". I also figured out through trial and error that I could create a database file as small as 512 bytes, but not 256 bytes. (I didn't try to find the absolute minimum file size I could create.) This script will add all the additional filegroups and files to that database.
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$s = new-object ('Microsoft.SqlServer.Management.Smo.Server') 'SQLTBWS\INST01'
$p = $s.MasterDBPath
$db = $s.Databases['TestLots']
for ($i=1;$i -lt 383;$i++) {
$fgname = 'Test_' + [string]$i
$fg = new-object ('Microsoft.SqlServer.Management.Smo.FileGroup') ($db, $fgname)
$db.FileGroups.Add($fg)
for ($j=1;$j -lt 33;$j++) {
$dbfname = $fgname + '_' + [string]$j
$dbfpname = $p + '\' + $dbfname + '.ndf'
#write-host $dbfname $dbfpname
$dbf = new-object ('Microsoft.SqlServer.Management.Smo.DataFile') ($fg, $dbfname)
$fg.Files.Add($dbf)
$dbf.FileName = $dbfpname
$dbf.Size = [double](512.0)
$dbf.GrowthType = 'None'
}
}
$db.Alter()
First I load the SMO library and connect with the SQL Server instance. Then I find out the directory holding the master data files (because a default data directory hasn't been set up on my system). Then I set the $db variable to the TestLots database object. I then set up a for loop to iterate 382 times and create a filegroup called Test_n, where n is the number of the filegroup. Once the filegroup is added, I use another for loop to iterate 32 times, creating a file called Test_n_m, where n is the filegroup and m is the file number within the file.
As I mentioned yesterday, using the right tool for the task is important, and having a variety of tools at your disposal truly helps get the job done faster.
Allen
|
-
Last February I wrote an article for Simple-Talk called Let PowerShell do an Inventory of your Servers. This script was useful to me then, and it continues to be so, but it recently ran into a glitch.
Here is the code in the script to gather data on each file used by a database:
$fgs = $db.FileGroups
foreach ($fg in $fgs) {
$files = $fg.Files
$outnm = ".\" + $svr + "\" + $instnm + $dbtype + "_" + $dbname + "_DataFiles.csv"
$files | select $db.Name, Name, FileName, Size, UsedSpace | export-csv -path $outnm -noType
}
It's nice and succinct, and as "set-based" as it can be, given that it has to iterate through the file groups. The problem is that underneath the hood, SMO sends a set of queries to SQL Server to create a temp table, then run "DBCC ShowFileStats", then join that temp table with sys.filegroups and sys.master_files catalog views to return the results in the Files collection.
The problem is that the underlying code contains a WHERE clause filtering the results FOR EACH FILE.
When this command runs the Reads counter (on my system) in Profiler averages around 78000, the CPU between 300 and 500, and Duration between 700 and 900, and this is run for each file. At a client site one database has over 10,000 files spread across almost 400 filegroups. I created a similarly configured database on my test system and the above code took about 2 hours to run. I replaced it with the following code:
$df = new-object "System.Data.DataSet" "dsConfigData"
$cn = new-object system.data.SqlClient.SqlConnection("Data Source=$inst;Integrated Security=SSPI;Initial Catalog=$dbname");
$q = "create table #tmpspc"
$q = $q + " ("
$q = $q + " Fileid int,"
$q = $q + " FileGroup int,"
$q = $q + " TotalExtents int,"
$q = $q + " UsedExtents int,"
$q = $q + " Name sysname,"
$q = $q + " FileName nchar(520)"
$q = $q + " ) "
$q = $q + "insert #tmpspc EXEC ('dbcc showfilestats')
"
$q = $q + " SELECT s.name AS [Name],"
$q = $q + " s.file_id AS [ID],"
$q = $q + " s.physical_name AS [FileName],"
$q = $q + " s.size * CONVERT(float, 8) AS [Size],"
$q = $q + " CASE when s.max_size = -1 then -1"
$q = $q + " else s.max_size * CONVERT(float, 8)"
$q = $q + " END AS [MaxSize],"
$q = $q + " CAST(CASE s.is_percent_growth"
$q = $q + " WHEN 1 THEN s.growth"
$q = $q + " ELSE s.growth * 8"
$q = $q + " END AS float) AS [Growth],"
$q = $q + " s.is_media_read_only AS [IsReadOnlyMedia],"
$q = $q + " s.is_read_only AS [IsReadOnly],"
$q = $q + " CAST(case s.state"
$q = $q + " when 6 then 1"
$q = $q + " else 0"
$q = $q + " end AS bit) AS [IsOffline],"
$q = $q + " s.is_sparse AS [IsSparse],"
$q = $q + " CAST(CASE when s.growth = 0 THEN 99"
$q = $q + " ELSE s.is_percent_growth"
$q = $q + " END AS int) AS [GrowthType],"
$q = $q + " CAST(CASE s.file_id"
$q = $q + " WHEN 1 THEN 1"
$q = $q + " ELSE 0"
$q = $q + " END AS bit) AS [IsPrimaryFile],"
$q = $q + " CAST(tspc.UsedExtents * convert(float, 64) AS float) AS [UsedSpace],"
$q = $q + " ( tspc.TotalExtents - tspc.UsedExtents ) * convert(float, 64) AS [AvailableSpace]"
$q = $q + " FROM sys.filegroups AS g"
$q = $q + " INNER JOIN sys.master_files AS s ON ( s.type = 0"
$q = $q + " and s.database_id = db_id()"
$q = $q + " and ( s.drop_lsn IS NULL )"
$q = $q + " )"
$q = $q + " AND ( s.data_space_id = g.data_space_id )"
$q = $q + " LEFT OUTER JOIN #tmpspc tspc ON tspc.Fileid = s.file_id
"
$q = $q + " drop table #tmpspc"
$daf = new-object "System.Data.SqlClient.SqlDataAdapter" ($q, $cn)
$daf.Fill($df)
$dtFiles = new-object "System.Data.DataTable" "dtFileData"
$dtFiles = $df.Tables[0]
$outnm = ".\" + $svr + "\" + $instnm + $dbtype + "_" + $dbname + "_DataFiles.csv"
$dtFiles | select $db.Name, Name, FileName, Size, UsedSpace | export-csv -path $outnm -noType
Once this code was in place I ran it and the process that took 2 hours originally finished in 9 seconds.
I've filed a Connect item for this here.
Just remember the important thing is that there's not ever just one tool to use. Understand when it's better to use T-SQL than PowerShell, or vice versa.
Allen
|
-
For those of you who may have missed it, the 24 Hours of PASS conference starts tonight at 8pm EDT!
I'll be presenting my introductory PowerShell for SQL Server session at 9pm EDT, so if you're interested in getting started with PowerShell, please join me.
You can register for the free virtual conference here.
See you tonight!
Allen
|
-
In working on a PowerShell script to load data into SQL Server, I decided to use the Invoke-SQLCMD cmdlet included in the snapins provided with SQL Server 2008, and found a couple of issues you should know about.
First, as a matter of best practices, if a stored procedure returns a scalar value, it should do so in an output parameter. The problem is that if the execute stored procedure statement is the only query sent to Invoke-SQLCMD, there is currently no mechanism to specify output parameters as such. The workaround, as specified by Mingwu Zhang on the SQL Dev team, is to declare a Transact-SQL variable to contain the output parameter, use that variable when calling the stored procedure, then select from that variable, all in a single "query" you send to Invoke-SQLCMD. Here's an example:
PS> $result = Invoke-SQLCMD -ServerInstance "SQLTBWS\INST01" -Database "ServerInventory" -query
"declare @comp_id int; exec insComputerSystem @comp_id OUTPUT, @name="SQLTBWS"; select @comp_id as comp_id"
PS> $computer_id = $result.comp_id
The object returned by the cmdlet here is a DataTable object, and by aliasing the variable as I did in the final select statement, the value supplied to the variable can be returned to the PowerShell script.
The second issue is actually a bug I found when testing my script. I've filed a Connect item at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=482833 for the bug. It identifies two related issues.
One is that once you connect to a database with Invoke-SQLCMD the connection remains after the cmdlet is finished. That means you can't drop the database, for example, without forcing existing connections to be closed. That's just an annoyance.
The bigger problem (and one you're probably not likely to encounter) is that if you do force an existing connection to be closed and drop the database, then recreate the same database and objects so you can re-run the same query, you then receive a transport-level error from the cmdlet because it won't reestablish the connection.
For the most part you won't run into either of these issues, but it's important to know they're there.
Allen
|
-
In the August 13th 2009 meeting of the Ohio North SQL Server Users Group (ONSSUG),
Paul Randal and his wife Kimberly Tripp, will run you through their top-ten database
maintenance recommendations, with a lot of tips and tricks along the way. This top ten
list distilled from almost 30 years combined experience working with SQL Server customers
and are geared towards making your databases more performance, more available, easier to
manage (to save you time!). Everything in this session will be practical and applicable
to a wide variety of databases. Topics covered and myths debunked include: backups, shrinks,
fragmentation, statistics, and much more! This presentation’s focus will be on SQL Server 2005
but we'll explain some of the key differences for 2000 and 2008 as well.
About our Speaker: Paul Randal
Paul Randal, SQL Server MVP, is the Managing Director of SQLskills.com, which he runs with
his wife, Kimberly Tripp. Paul started in the industry in 1994 working for DEC on the VMS
file system and check/repair tools. In 1999 he moved to Microsoft to work on SQL Server,
specifically on DBCC. In 2007, after nearly 9 years on the SQL Server team, Paul left Microsoft
to join Kimberly running SQLskills.com, and to pursue his passion for authoring, teaching,
and consulting. Paul regularly presents at conferences and user groups around the world on
high-availability, disaster recovery, database maintenance, and Storage Engine internals.
He writes for TechNet Magazine and his popular blog is at
http://www.sqlskills.com/blogs/paul/
Microsoft Corporation
6050 Oak Tree Blvd. Suite 300
Cleveland, OH 44131
Registration:
To register, go to www.bennettadelson.com/sql
and click on the link "reserve your seat".
Allen
|
|
|
|
|
|