SQL Server Data Tools (SSDT) was introduced as a replacement to the old Business Intelligence Development Studio that’s been around since SQL 2005. But it does more than just create Integration Service or Analysis Service projects. Microsoft has taken the old BIDS and combined it with the Data parts of Visual Studio for a whole new tool; SSDT.

SSDT is a new way to develop SQL database objects. It ties into Visual Studio 2010 and now developers can work in a familiar environment. SSDT can be installed as a feature of a SQL 2012 installation or as a separate download from Microsoft. If you’ve already installed VS 2010 Professional or above you’ll need to upgrade to with SP1 first, if you haven’t already. If you’re not running VS 2010 SSDT will install a VS 2010 shell for your use.

Working Online

Working against an online database is done very similar to working in Management Studio. Open SSDT, then open SQL Server Object Explorer and create a connection to your server. Once you’ve done that you can view databases and their objects. This is very similar to what you see in SSMS. To open a new query window click the New Query icon (right button in SQL Object Explorer). The query window is a slimmed down SSMS query window, but the same functionality is there; there’s buttons on the toolbar for Estimated and Actual Execution plans or viewing the results in a grid or text. You’ll see your results and messages in separate tabs on the bottom, or like VS you can switch where the code and results display. Intellisense is available in the query pane. The main difference is that you execute your code with Ctrl + Shift + E instead of F5. The little green Execute arrow is still there if you rather use that method.

Working Offline

My favorite features come when you work in disconnected mode.When you’re disconnected you’re working in a local sandbox, completely separated from other users. You can work on tuning that stored procedure while not affecting any application.

There’s a few ways to do this, but I found the easiest is to right click on the database you want to create a copy of, then choose “Create New Project”. You’ll get the screen below where you can set your project options. One of the most helpful is the last one, on file structure. You can organize your project by schema, by object, by both, or by neither. I personally prefer both.

After you click Start SSDT will create the objects in your new project. In the Solution Explorer are all the objects created and sorted as you chose when you created the project. You’ll also see a new entry in the SQL Object Explorer pane, called LocalDB\ProjectName. You’ll also notice that the database doesn’t appear to have any objects yet. Press F5 to build and deploy your project, refresh the SQL Object Explorer and voila! There they all are.

Note that this only creates the objects. It doesn’t populate the tables.But that’s a piece of cake. Back in SQL Object Explorer, go to your original source database, right click on the table that has the data you want, and choose View Data. A new query window will open showing the records just like SSMS does. You can change the number of records returned as well, though I don’t see a way of placing a filter on the results. Anyway, if you click on the Script button a new query window will open with an INSERT statement for each record in the results. Change the connection to the LocalDB database,  run the query, and congratulate yourself. You’ve just populated your local table! You can query the LocalDB, update or delete records, whatever you need, without affecting anyone else.

First a word about that LocalDB database you just created. Don’t confuse it with a database on your local instance. You may not even have one. What SSDT uses is a database it creates that doesn’t need SQL. If you look at the properties of the LocalDB instance it will say “Microsoft SQL Express Edition”, but this isn’t the Express Edition available as a download.It doesn’t run as a service, and as far as I can see it’s only available when SSDT is running. If you drill into the directory where you created your project you’ll spot a Sandbox folder, and inside will be a .mdf and .ldf file for your database. There are some warning about using LocalDB, mainly that it doesn’t support all SQL features. I know FILESTREAM is one.You’ll probably want to check on BOL for others.

Snapshots and Schema Compare

My next favorite feature is the ability to create snapshots of your project and to compare schemas between databases.

Snapshots are a representation of your database project at a given moment. Back in Solution Explorer, right click on the project name and select Snapshot Project. SSDT will create a file with the .dacpac extension. The default name is the database with a timestamp but you can call it anything you like. You can create snapshots anytime, at different stages of your development. And that’s where Schema Compare really shines.

Let’s say you connect to a database and create a new database project. And you create a snapshot before you start development. While developing you make changes to existing objects or create new ones. Now you want to create a change script, but maybe you forgot what objects you touched. And you can’t go back and compare to your source database as maybe it has changed as well from other users. You can still use Schema Compare and use the snapshot files you created as a source or target. Schema Compare will work the same way it has against “real” databases, showing you the differences in the schemas. You can either create a script to run or click the Update button to apply your changes. You can filter by schema or object if you don’t want to compare large projects.

There’s more to SSDT than just what I’ve written here. I’ll try to write another post on SSDT going a bit deeper. In the meantime, check out SSDT in Books Online, or just Google it. This is a very handy tool for SQL developers to add to their arsenal.

Most of us are familiar with using the TOP keyword in a T-SQL query. Basically, TOP will return a specified percentage or number of records. For instance, to see the top 100 sales orders by total price you may run a query similar to this one.

SELECT TOP 100 SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice, LineTotal
FROM AdventureWorks2012.Sales.SalesOrderDetail
ORDER BY LineTotal DESC;

In SQL Server 2012 there’s a new function we can use, OFFSET – FETCH. You can get the same results as above using this new functionality;

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice, LineTotal
FROM AdventureWorks2012.Sales.SalesOrderDetail
ORDER BY LineTotal DESC
OFFSET 0 ROWS FETCH NEXT 100 ROWS ONLY;

When you use OFFSET you specify the starting position. To begin at the top (or bottom you start at 0, which I did above. But you can start anywhere. To see the next hundred records you’d say OFFSET 99. You can use a variable for OFFSET, but OFFSET must be an integer that is 0 or greater.

FETCH NEXT is the number of records to return. In my case I’m only returning 100 rows but you can specify any valid number. It can come from a variable or a subquery.

The syntax has a few options. You can state ROW instead of ROWS, SQL will take either. That means OFFSET 1 ROW is just as valid as OFFSET 1 ROWS.  Also NEXT and FIRST are the same, use either one in your statement.

There are some differences between TOP and OFFSET – FETCH.

• The ORDER BY clause is required for OFFSET, not for TOP. But you can sort in either direction.
• FETCH is not required. If you want to skip the first 100 records just state OFFSET 99;
• FETCH doesn’t support WITH TIES or PERCENT.  You can approximate the percentage by using a subquery but you may not get the same number of records as using TOP n PERCENT

SELECT SalesOrderID, SalesOrderDetailID, ProductID, OrderQty, UnitPrice, LineTotal
FROM AdventureWorks2012.Sales.SalesOrderDetail
ORDER BY LineTotal DESC
OFFSET 0 ROWS FETCH NEXT (SELECT COUNT(*) / 10 FROM AdventureWorks2012.Sales.SalesOrderDetail) ROWS ONLY;

So, play around with OFFSET – FETCH.

This post is about working with FileTables in SQL Server 2012. I’m not going to go over FileTables in general or how to create them as there are many blogs walking through the procedure and setting up the prerequisites; I’ve put links to some of them in the Resources section at the bottom of this page.

I’m using a virtual server running the Developer Edition of SQL 2012 on Windows Server 2008 R2 called VMBI2012, hosted in Virtual Box. That’s not a requirement and you can duplicate everything here on the same box, but I wanted to point out an issue with certain file types when trying to access them directly. Also you don’t need the Developer Edition of SQL, Microsoft says that FileTables are supported in all editions.

The only part you may need to add is the Semantic database. It’s not an option when you install SQL but look for the SemanticLanguageDatabase.msi package on your installation media or download from Microsoft here. After you run the package attach the database (it’s placed in C:\Program Files\Microsoft Semantic Language Database\ by default but you can move it). Then just register it;

EXEC sp_fulltext_semantic_register_language_statistics_db @dbname = N’semanticsdb’;

OK, so I’ve configured my server to use Filestreaming, and I added the semantic database. I created a database that contains a FileTable called Documents and also set up a full text catalog. The script I used is as follows, if you want to follow along at home;

CREATE DATABASE DenaliDemoDB
ON  PRIMARY
(
NAME = 'DenaliDemoDB_data',
FILENAME = 'C:\SQL2012\DATA\DenaliDemoDB_data.mdf',
SIZE = 512000KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 102400KB
),
FILEGROUP FSSecondary CONTAINS FILESTREAM
(
Name = FileTableData,
FILENAME = 'C:\SQL2012\FileTables\Data'
)
LOG ON
(
NAME = 'DenaliDemoDB_log',
FILENAME = 'C:\SQL2012\DATA\DenaliDemoDB_log.ldf',
SIZE = 102400KB,
MAXSIZE = 2048GB,
FILEGROWTH = 25600KB
)
WITH FILESTREAM
(
NON_TRANSACTED_ACCESS = FULL,
DIRECTORY_NAME = 'MyScripts'
);

USE DenaliDemoDB;

CREATE TABLE dbo.Documents AS FILETABLE
WITH
(
FILETABLE_DIRECTORY = 'DocumentsFT',
FILETABLE_COLLATE_FILENAME = database_default
);

CREATE FULLTEXT CATALOG Documents_Catalog WITH ACCENT_SENSITIVITY = ON;

CREATE FULLTEXT INDEX ON dbo.Documents
(
name
LANGUAGE 1033
STATISTICAL_SEMANTICS,
file_type
LANGUAGE 1033
STATISTICAL_SEMANTICS,
file_stream
TYPE COLUMN file_type
LANGUAGE 1033
STATISTICAL_SEMANTICS
)
KEY INDEX PK__Document__5A5B77D5962E5D8F
ON Documents_Catalog
WITH CHANGE_TRACKING AUTO, STOPLIST = SYSTEM;

There’s a few things to point out here. First, notice the name of the KEY INDEX. This is the name SQL assigned when I created the FileTable, your name will most likely be different. You can always rename it before you create the catalog. Second, that for each column I’m declaring STATISTICS_SEMANTICS. This is to take advantage of the semantic searching, but it’s optional. If you create it in SSMS it’s just a checkbox, like so.

After that I created 4 .txt files called text1.txt through text4.txt. The content in each is simple:

1. This is text one. Magic
2. This is text 2. French Roast
3. This is text 3. French Roast and Columbian
4. This is text four. Magic and Wizards

If you use T-SQL to query the table you’ll see 4 records, one for each file. If you browse to the shared folder you’ll see the same files. However if you try to open any of them you get this error . According to Books Online that’s because FileTables don’t support memory-mapped files. Paint files are another. However you probably don’t want users editing files directly on the server anyway. Instead use another computer and map a drive to the shared folder and edit it that way. I used my host machine to map to the vm’s shared director to edit text4.txt. Now it says “What happens behind the scenes when we manipulate this file inside SQL?”

Now we’re set to work with the FileTable. First, to find the name of the shared directory use the FILETABLEROOTPATH function with the name of the FileTable as a parameter.

SELECT FILETABLEROOTPATH('dbo.Documents')

Use the GetFileNamespacePath method of the file_stream column to return the path of each file in the FileTable. It takes two parameters. The first is is_full_path which returns the file’s path, with 0 (the default) returning the relative path and 1 returning the UNC path. The second is @option, where 0 (the default) shows the NetBIOS name, 1 returns the server name, and 2 returns the complete path.

SELECT file_stream.GetFileNamespacePath(1, 2) as FileLocation
FROM dbo.Documents

Finally, I can use the full text catalog I set up earlier to search the file_stream column. If I search for the word “french” I should get back text2.txt and text3.txt.

SELECT file_stream.GetFileNamespacePath() as FileLocation
FROM dbo.Documents
WHERE CONTAINS(file_stream, 'french')

Semantic Search builds on the capabilities of the full text catalog. Not only can you search for matches but also by key phrases. Using the SEMANTICKEYPHRASETABLE function can show documents by searching for a keyphrase and the score of a likely match.

SELECT FILETABLEROOTPATH('dbo.Documents') + '\' + d.name AS FilePath, s.keyphrase, s.score
FROM dbo.Documents AS d
INNER JOIN SEMANTICKEYPHRASETABLE(dbo.Documents, *) AS s
ON d.path_locator = s.document_key
WHERE s.keyphrase LIKE '%r%'
ORDER BY s.score DESC;

SEMANTICSSIMILARITYDETAILSTABLE compares two documents and shows the similarity between the two…

DECLARE @Document1 hierarchyid;
DECLARE @Document2 hierarchyid;

SET @Document1 = (SELECT path_locator FROM dbo.documents WHERE name = 'text2.txt');
SET @Document2 = (SELECT path_locator FROM dbo.documents WHERE name = 'text3.txt');

SELECT *
FROM SEMANTICSIMILARITYDETAILSTABLE
(
dbo.Documents,
file_stream, @Document1,
file_stream, @Document2
)
ORDER BY score DESC;

Finally, SEMANTICSIMILARITYTABLE can be used to compare all documents to a single file.

DECLARE @Document3 hierarchyid;

SET @Document3 = (SELECT path_locator FROM dbo.documents WHERE name = 'text1.txt');

SELECT s.source_column_id, s.matched_column_id, FILETABLEROOTPATH('dbo.Documents') + '\' + d.name AS FilePath, s.score
FROM dbo.documents AS d
INNER JOIN SEMANTICSIMILARITYTABLE
(
dbo.Documents,
file_stream, @Document3
) AS s
ON d.path_locator = s.matched_document_key
ORDER BY score DESC;

References

To get more information on FileTables or Semantic Searches start with Books Online

FilesTables in Books Online
http://msdn.microsoft.com/en-us/library/ff929144.aspx

Semantic Search on BOL
http://msdn.microsoft.com/en-us/library/gg492075.aspx

Bob Beauchemin has some very informative posts on filestreaming and filetables
http://sqlskills.com/BLOGS/BOBB/category/Filestream-Storage.aspx

Finally, here’s a few posts that walk through the process of setting up file tables.

Beyond Relational – File Table in SQL Server 2012 – Great flexibility to manage filestream data
http://beyondrelational.com/modules/24/syndicated/389/Posts/11829/file-table-in-sql-server-2012-great-flexibility-to-manage-filestream-data.aspx

SQL Server Central
http://www.sqlservercentral.com/blogs/sqlservernotesfromthefield/2012/01/03/sql-server-2012-filetable-part-1/

Just a quick little post about an issue that pops up occasionally while I slowly work my way back into SQL after my test.

There are a lot of SQL databases servers that have been configured to use Database Mail to send email alerts for failed jobs, query results, alerts, or other tasks that require some kind of notification. Database Mail, introduced with SQL 2005, is the much improved way to send emails, far better than the older SQL Mail option. If you’re not familiar with Database Mail, go read up on it first on Books Online.

However I’ve found that some DBAs that set up Database Mail aren’t familiar with how to troubleshoot it when users complain they haven’t received expected emails from SQL. Actually it’s pretty simple. Microsoft has included some system views in the msdb database to help you out, and a log to view errors.

If you don’t want to use T-SQL you can view the database mail logs from SSMS. Just right click on Database Mail in the Object Explorer and choose View Database Mail Log (of course this is after you’ve configured Database Mail). Here you can see the status of db mail and any emails that didn’t get sent, including the reason for the failure.

You can get the same information by querying msdb.dbo.sysmail_event_log, including filters for event_type and a specific date if you want;

SELECT * FROM msdb.dbo.sysmail_event_log
WHERE event_type = 'error'
AND log_date >= dateadd(d, -1, getdate())

You can use other views to see the content of the emails. SYSMAIL_FAILEDITEMS shows emails that didn’t get sent, SYSMAIL_SENTITEMS shows emails that were sent successfully, and SYSMAIL_ALLITEMS  combines the two. You can filter by the email_id you got from the log, or see all by a date.

SELECT *
FROM msdb.dbo.sysmail_faileditems
WHERE mailitem_id = 56299;

OK. So you’ve found out that there was a problem sending emails through SQL. You’ve looked at the log to find the problem and fixed it. Now you want to resend the failed emails. Couldn’t be easier. Just query one of the msdb views for the emails you want to resend, store relevant fields in variables, then call the sp_send_dbmail procedure passing in the variables. You can use it for a single item, or wrap it in a loop to resend multiple emails. I use a script like the one below.

DECLARE @to        varchar(max)
DECLARE @copy    varchar(max)
DECLARE @title    nvarchar(255)
DECLARE @msg    nvarchar(max)
SELECT @to = recipients, @copy = copy_recipients, @title = [subject], @msg = body
FROM msdb.dbo.sysmail_faileditems
WHERE mailitem_id =  56299
EXEC msdb.dbo.sp_send_dbmail
@recipients = @to
@copy_recipients = @copy
@body = @msg,
@subject = @title,
@body_format = 'HTML';

And that’s it. Simple, no?

This is just a quick post while I take a little break from studying for the 70-448 exam next month.

We have 4 SQL 2005 servers and 4 SQL 2008 servers in our environment, 2 of each in 2 data centers. All SQL services on all 8 instances are configured to start manually; we want to look at logs and events before starting up SQL in cases where the servers are restarted with no warning.

However the SQL database services on the 4 instances in one data center are still starting automatically. I’ve double checked all the settings and I’m not aware of any monitoring software that would restart the services. Until I find out what process is starting these services I want a way to be notified of when the services start up.

I’ve already set up a stored procedure to run when the SQL service starts so I thought I’d use that. At first I thought I’d just use SQL mail, but that wouldn’t work if the Agent wasn’t also running. So I decided on using a Powershell script.

It couldn’t be easier. I set few variables with email addresses and the current time, set the SMTP server to our Exchange server, and create a new Net.Mail.SMTPClient. Then it’s just sending the emails with the variables.

$now = Get-Date
$from = "someone@work.com"
$to = "monitoring@work.com"
$subject = "SQL Service on Server 1 Started"
$body = "The SQL service on Server 1 has started at " + $now
$smtpServer = "email.work.com"
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($from, $to, $subject, $body)

I save the above as SendMail.ps1. The only downside is that I call it with xp_cmdshell;

EXEC xp_cmdshell 'powershell -Command "C:\SQL2005\Scripts\SendMail.ps1"'

And that’s it. I’d be interested in hearing any ideas you might have to improve my process, or even better, how to trap the process that’s starting my SQL services.

A few months back I wrote a post about how I wanted a way to automate a backup of the encryption key used by our SQL 2008 reporting servers. I thought that Powershell would be the way to go, but I ran into a few issues. First, I couldn’t see a way to use Powershell to access the key. I could always use rsKeyMgmt from a command line, but I’d have to feed in a Y since I couldn’t suppress a prompt. I found a way to do that, but I didn’t want the password to be saved inside a batch file. And I wanted to copy the key file to a second location, in case I needed to use it to restore a report server. You can read my earlier post here.

One day, while I had my twitter feed open on my desktop, I saw a tweet from Laerte Junior (Blog | Twitter). In it he mentioned a post he wrote about accessing the report server WMI objects. And finally everything clicked into place.

The first thing I did was to install the SQLPSX modules on our report servers. The modules are available on Codeplex and you can download them here. Next I created a profile for all users to load the SQL Server snapins. I use Invoke-Sqlcmd to query a database.

Add-PsSnapin SqlServerCmdletSnapin100
Add-PsSnapin SqlServerProviderSnapin100

The final step was to create a table inside a database to store a password to use for the encryption key file. I figured I could easily control access to that password to only those who needed to know it. Now I was ready to create a script to do everything I wanted.

Getting the password out and storing it in a variable was easy:

$results = Invoke-Sqlcmd -Query "SELECT pwd FROM AdminDB.dbo.SSRS" -ServerInstance "Java"

Thanks to Laerte Junior I know what namespace, class and method to use. I simply need to pass in the password stored in my variable.

$SSRSClass = Get-Wmiobject -namespace "root\microsoft\sqlserver\reportserver\rs_mssqlserver\v10\admin" -class "MSReportServer_ConfigurationSetting"

$key = $SSRSClass.BackupEncryptionKey($results.pwd)

One thing to note. The server in my example is the default instance and is named “JAVA”. So when I reference the namespace I use the default instance of “rs_mssqlserver”. If you’re using a named instance just change it to rs_YourInstanceName.

Now that I’ve backed up the key I need to create the file.

$stream = [System.IO.File]::Create("c:\\SSRS.snk", $key.KeyFile.Length)
$stream.Write($key.KeyFile, 0, $key.KeyFile.Length)
$stream.Close()

The last thing I do is to copy the file to a second, safe location.

Copy-Item "C:\SSRS.snk" "\\DEVSERVER\SQLBackups"

And that’s all there is to it. I can use the Report Server Configuration Manager to restore the key when I need to.

Now I have this script on all of our report servers, and it runs after we promote new reports. There’s a few things I’ve been meaning to add, like deleting the old file before I create the new one. And I should put in error handling. I’ll be putting that into v2.

A few months back I wrote a post about how I wanted a way to automate a backup of the encryption key used by our SQL 2008 reporting servers. I thought that Powershell would be the way to go, but I ran into a few issues. First, I couldn’t see a way to use Powershell to access the key. I could always use rsKeyMgmt from a command line, but I’d have to feed in a Y since I couldn’t suppress a prompt. I found a way to do that, but I didn’t want the password to be saved inside a batch file. And I wanted to copy the key file to a second location, in case I needed to use it to restore a report server. You can read my earlier post here.

One day, while I had my twitter feed open on my desktop, I saw a tweet from Laerte Junior (Blog | Twitter). In it he mentioned a post he wrote about accessing the report server WMI objects. And finally everything clicked into place.

The first thing I did was to install the SQLPSX modules on our report servers. The modules are available on Codeplex and you can download them here. Next I created a profile for all users to load the SQL Server snapins. I use Invoke-Sqlcmd to query a database.

Add-PsSnapin SqlServerCmdletSnapin100
Add-PsSnapin SqlServerProviderSnapin100

The final step was to create a table inside a database to store a password to use for the encryption key file. I figured I could easily control access to that password to only those who needed to know it. Now I was ready to create a script to do everything I wanted.

Getting the password out and storing it in a variable was easy:

$results = Invoke-Sqlcmd -Query "SELECT pwd FROM AdminDB.dbo.SSRS" -ServerInstance "Java"

Thanks to Laerte Junior I know what namespace, class and method to use. I simply need to pass in the password stored in my variable.

$SSRSClass = Get-Wmiobject -namespace "root\microsoft\sqlserver\reportserver\rs_mssqlserver\v10\admin" -class "MSReportServer_ConfigurationSetting"

$key = $SSRSClass.BackupEncryptionKey($results.pwd)

One thing to note. The server in my example is the default instance and is named “JAVA”. So when I reference the namespace I use the default instance of “rs_mssqlserver”. If you’re using a named instance just change it to rs_YourInstanceName.

Now that I’ve backed up the key I need to create the file.

$stream = [System.IO.File]::Create("c:\\SSRS.snk", $key.KeyFile.Length)
$stream.Write($key.KeyFile, 0, $key.KeyFile.Length)
$stream.Close()

The last thing I do is to copy the file to a second, safe location.

Copy-Item "C:\SSRS.snk" "\\DEVSERVER\SQLBackups"

And that’s all there is to it. I can use the Report Server Configuration Manager to restore the key when I need to.

Now I have this script on all of our report servers, and it runs after we promote new reports. There’s a few things I’ve been meaning to add, like deleting the old file before I create the new one. And I should put in error handling. I’ll be putting that into v2.

This month’s T-SQL Tuesday is being hosted by Allen White (Blog | Twitter). The topic is sharing your T-SQL scripts that you use to make your job easier. Below is one of mine.

One of the things I like to keep track of is table growth; that is, which tables grow the most over a period of time. This helps me to determine if I have enough database space allocated, and gives me a rough estimate of when the servers will need more drive space added.

At first I just monitored the size of the databases files and watched how big they were getting and how much or little free space was still available. But I also wanted a way to see just where that growth was. So I would run sp_spaceused on occasion on our biggest tables and compare each set of results to the previous set.

However this is not very efficient. As our application grew and more and more tables were being added it was harder to know just which tables were being affected the most.

So finally I began to use two un documented procedures to look at all tables in all databases. The first is sp_MSForEachDB, the second is sp_MSForEachTable. Each does just what it sounds like; runs a query against all databases or loops through all tables. I’m not going to go into detail here as neither procedure is new and each has been written about countless times before.

My procedure builds a dynamic SQL statement, getting the schema name and table name using sp_MSForEachTable. Part of that statement is an INSERT statement so I can store the results. Then I run the dynamic statement against all user databases on the server.

USE [PerfDB]
GO

CREATE TABLE [dbo].[TableSpace](
[RunDate] [datetime] NOT NULL,
[DatabaseName] [nvarchar](128) NULL,
[SchemaName] [nvarchar](128) NULL,
[TableName] [nvarchar](128) NOT NULL,
[NumRows] [int] NOT NULL,
[Reserved] [nchar](16) NOT NULL,
[DataUsed] [nchar](16) NOT NULL,
[IndexUsed] [nchar](16) NOT NULL,
[Unused] [nchar](16) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[TableSpace] ADD  CONSTRAINT [DF_TableSpace_RunDate]  DEFAULT (getdate()) FOR [RunDate]
GO

CREATE PROCEDURE [dbo].[prc_TableSpace]
AS

SET NOCOUNT ON;

DECLARE @SQL varchar(8000)

SELECT @SQL = '
IF ''@'' <> ''master'' AND ''@'' <> ''model'' AND ''@'' <> ''msdb'' AND ''@'' <> ''tempdb''
BEGIN
USE [@] EXECUTE sp_MSForEachTable ''INSERT INTO PerfDB.dbo.TableSpace (TableName, NumRows, Reserved, DataUsed, IndexUsed, Unused) EXEC sp_spaceused ''''?'''';
UPDATE PerfDB.dbo.TableSpace SET SchemaName = LEFT(''''?'''', CHARINDEX(''''.'''', ''''?'''', 1) - 2) WHERE SchemaName IS NULL;
UPDATE PerfDB.dbo.TableSpace SET DatabaseName = ''''@'''' WHERE DatabaseName IS NULL; ''
END
'

EXEC sp_MSforeachdb @SQL, '@'

UPDATE PerfDB.dbo.TableSpace
SET SchemaName = REPLACE(SchemaName, '[', '')

I set up a weekly job that runs during a period of inactivity. I also have a job that runs an SSIS package to collect the table results into a central repository. Then I can run the procedure like the one below to show me how much every table grew in the past week, or a similar one that shows the growth over a period of time.

CREATE PROCEDURE [dbo].[TableSizeCompare]
AS

SET NOCOUNT ON

CREATE TABLE #myTable (
ServerName        NVARCHAR(25),
RunDate            DATETIME,
DatabaseName    NVARCHAR(128),
SchemaName        NVARCHAR(128),
TableName        NVARCHAR(128),
NumRows            INT,
ReservedInKB    INT,
DataUsedInKB    INT,
IndexUsedInKB    INT,
RowNum            TINYINT)

;WITH TableSpaceCTE (ServerName, RunDate, DatabaseName, SchemaName, TableName, NumRows,
ReservedInKB, DataUsedInKB, IndexUsedInKB, RowNum) AS
(
SELECT ServerName, RunDate, DatabaseName, SchemaName, TableName, NumRows,
CONVERT(INT, LEFT(Reserved, LEN(Reserved) - 3)) AS ReservedInKB,
CONVERT(INT, LEFT(DataUsed, LEN(DataUsed) - 3)) AS DataUsedInKB,
CONVERT(INT, LEFT(IndexUsed, LEN(IndexUsed) - 3)) AS IndexUsedInKB,
ROW_NUMBER() OVER (PARTITION BY ServerName, DatabaseName, SchemaName, TableName ORDER BY RunDate DESC) AS RowNum
FROM PerfDB_DW.dbo.TableSpace
)
INSERT INTO #myTable
SELECT ServerName, RunDate, DatabaseName, SchemaName, TableName, NumRows,
ReservedInKB, DataUsedInKB, IndexUsedInKB, RowNum
FROM TableSpaceCTE
WHERE RowNum <= 2

SELECT
MaxRun.ServerName
, MaxRun.DatabaseName
, MaxRun.SchemaName
, MaxRun.TableName
, MaxRun.RunDate AS LastRunDate
, MinRun.RunDate AS PriorRunDate
, MaxRun.NumRows AS CurrentNumRows
, MinRun.NumRows AS PriorNumRows
, MaxRun.NumRows - MinRun.NumRows AS NumRowGrowth
, MaxRun.ReservedInKB AS CurrentReservedInKB
, MinRun.ReservedInKB AS PriorReservedInKB
, MaxRun.ReservedInKB -  MinRun.ReservedInKB AS ReservedGrowthInKB
, MaxRun.DataUsedInKB AS CurrentDataUsedInKB
, MinRun.DataUsedInKB AS PriorDataUsedInKB
, MaxRun.DataUsedInKB -  MinRun.DataUsedInKB AS DataUsedGrowthInKB
, MaxRun.IndexUsedInKB AS CurrentIndexUsedInKB
, MinRun.IndexUsedInKB AS PriorIndexUsedInKB
, MaxRun.IndexUsedInKB -  MinRun.IndexUsedInKB AS IndexUsedInKB
FROM
(SELECT * FROM #myTable WHERE RowNum = 1) AS MaxRun
INNER JOIN
(SELECT * FROM #myTable WHERE RowNum = 2) AS MinRun
ON MaxRun.ServerName = MinRun.ServerName AND MaxRun.DatabaseName = MinRun.DatabaseName
AND MaxRun.SchemaName = MinRun.SchemaName AND MaxRun.TableName = MinRun.TableName
ORDER BY MaxRun.ServerName, MaxRun.DatabaseName, MaxRun.SchemaName, MaxRun.TableName

This is the last in my series on monitoring performance on a server hosting Microsoft’s SQL Server Reporting Service. In Part 1 I show a way to monitor reports that are running. In Part 2 I shared a few queries to see historical report performance. In Part 3 I discuss building an SSAS cube that lets me analyze the performance in Excel or SSRS reports. Today I’m going to look on the reporting server itself.

A good place to start is with report server performance counters.  Some of the ones I keep a close eye on are the Requests Queued, Requests Executing, Requests Rejected, and Tasks Queued. Overall these counters give me a good picture of how active the server is. Continuous high values in any of them may tell me that some reports may be running sluggish. Another similar counter is Active Connections. Ordinarily this number is pretty small, but I’ve seen issues when a high number of active connections caused CPU and memory issues. Though I haven’t been able to pinpoint yet why it’s happening at least I get a heads up that it’s about to.  Memory Pressure State is also useful. and I’ll discuss that a little more later. You can view the full list of available counters on Books Online.

Secondly, look at the report server error logs. Anytime a report runs it’s logged in these files. Usually it’s just the fact that the report was  processed. But if you see reports that show a status other than rsSuccess in the ExecutionLog2 view the reason is probably here as well. in this screenshot I can identify a report that probably didn’t render fully as there’s a data type mismatch, most likely in the data source of the report.  Then I can check other times this report ran to see if there were similar errors. That could help in determining if the fault is with the report or with the data for the time this report ran. Remember that you can see the report parameters back in the ExecutionLog2 view.

Ted Krueger, a SQL MVP who writes for LessThanDot (Blog | Twitter) wrote an excellent post on the logs here. And Books Online also shows you how to  control the logs in Server Trace Log; what info to store, how long to store them, and where they can be found.

Finally, check out the settings in the rsreportserver.config file. Again, Books Online has a breakdown of each option (RSReportServer Configuration File). Some of the settings here can really be helpful in getting the best performance out of your SSRS server. Remember the Memory Pressure State performance counter I mentioned earlier? Here is where you can define what memory pressure is. There are some great articles online about what each option is used for. One of the best (Exploring the RSReportServer.config File) was written by Jes Schultz Borland (Blog | Twitter) for SQL University.

I hope I was able to help out a bit. Definitely check out the links I’ve provided if you need more information. All those authors have written about SSRS much better than I can.

Other Resources

There’s another very useful post on Reporting Services Performance Optimizations on the SQLCAT site. Especially helpful is the section on Memory Configurations on both SQL 2008 and SQL 2005 instances.

Also, check out Configuring Available Memory for Report Server Applications in Books Online.

I’ve had a few comments on a post I wrote earlier, about using a variable to create and populate an Excel spreadsheet. This is how I did it, step by step.

Let me recap what I’m doing. I have 4 servers that I collect some statistics from weekly. In the package, I want to gather total and available drive space, database size used and free, and table size. That is done via SQL scripts on each server every Sunday morning. On Sunday evenings I run a package to pull the data into a central repository. Finally I manually run another package to send data from the last two weeks to a local Excel spreadsheet.

The full package isn’t very complicated. The whole workflow looks like this:

First, I set up an OLE DB Source connection and an Excel Destination. The Excel file already exists. Then I created two variables, one called SheetName and the other called SheetTable. They’re both at the package level (my package is called “DBCompare”) and both are strings.

Both script tasks are similar. Each changes the value of the SheetName variable and uses code to create a table on the new Excel spreadsheet. Each also has the 2 variables set as ReadWrite. This code is from the DBCompare script task.

The Execute SQL task is where I found the issue I discussed in my original post. I wasn’t able to set the Source Variable on the screen below, but I could using the properties page. Afterwards, it shows up correctly.

After that it’s just a straightforward dataflow from my source to my destination…

…and after that the control flow repeats to get the size of the drives.