Entries Tagged 'SQL Server' ↓

I learned a cool little trick the other day, something that will help me immensely with testing my SQL Server 2005 security setup. The trick I learned is the Execute As clause in T-SQL.

I have a database that I want certain users to be able to work with, but not others. In the past I would set up security as I thought it should be set up and then ask a user to test it out. This is a pain in the butt, to put it nicely. First of all, people are busy and don’t always want to be bothered to test things for a DBA. Second of all, people don’t like to know they can’t do things. With Execute As, I no longer have to bug any users. I can “Execute As” that user.

For example, let’s suppose I don’t want UserA to be able to Select from the ExecutiveSalary table in the Employee database. If I’m a member of the sysadmin role (which I am) I can simply say:

Execute as user=’UserA’

Select *

From Executive Salary

SQL Server will try to do the Select statement using UserA’s permissions rather than my own, and UserA never has to know. This is just one of those easy tricks that are sometimes easy to miss, but that really make an administrator’s life easier.

I thought I’d share a little information with you today about something I came across when upgrading to SQL Server 2005, so you can avoid some of the frustrations I had to deal with.

In our SQL Server 2000 environment we had a few DTS packages which import data every night from text files via the scheduler. These packages were a pain in the butt to set up, because they were importing data from text files that weren’t delimited. This meant defining all those columns as fixed width using that horrible interface. Anyhow, when upgrading to SQL Server 2005 the last thing I wanted to do was re-create these DTS packages.

Luckily SQL Server 2005 has a conversion utility to convert these DTS packages to SSIS packages. This worked well and I had my packages in SQL Server 2005. I created a job to run my SSIS packages and that worked fine too. The problems arose when I tried to schedule these jobs. No matter what I did, the scheduled job always failed (even though I could right click on the job name, click Start Job at Step.. and the job would run successfully).

Each time I would get an error that said, “Failed to decrypt protected XML node “PackagePassword” with error 0×8009000B”. I had no real password protections on this package, so I really wasn’t sure what this error meant. Oh, I forgot to mention one thing: when we upgraded to SQL Server 2005 we also started on a new server, one that was 64-bit. It turns out this is an important detail.

Anyhow, that was the problem. The solution is a simple one, once you know it. When setting up your job, rather than defining the Type as “SQL Server Integration Services Package” as below,

instead choose “Operating system (CmdExec)”.

In the Command Window enter the following string:
“C:\Program Files (x86)\Microsoft SQL Server\90\DTS\Binn\dtexec.exe” /SQL “namehere” /SERVER ServerNameHere /MAXCONCURRENT ” -1 ” /CHECKPOINTING OFF /REPORTING E

Make sure to replace namehere with the name of your SSIS Package and ServerNameHere with the name of your server. Schedule this and it should run, using the old DTS execution engine.

As a database administrator you may not know every single database on your SQL Server intimately, though you may still be expected to make it perform like you do. No matter how many times you explain the importance of indexes and even what an index is, sometimes your users just don’t get it, just as they may explain their queries and data to you many times, but it just doesn’t make sense. Luckily, Microsoft has developed a set of tools that can help: the SQL Server 2005 Performance Dashboard Reports.

It is pretty simple to install the Performance Dashboard once you have downloaded it from the above link. I will point you to this link which walks you through installation as they to a fine job of describing the process.

The great thing about these reports is that they keep track of activity on your server for you. You don’t have create a trace or anything like that. Simply have your users go about their every day jobs of using their applications that go against the SQL Server and the information that Performance Dashboard needs will be captured.

Once installed you access the reports by right clicking either the server name or database name in Management Studio and then selecting “Reports” and then “Custom Reports…”.

Navigate to the folder that chose to put the reports in during installation (the default is C:\Program Files\Microsoft SQL Server\90\Tools\PerformanceDashboard). You will see a list of reports. I suggest starting with performance_dashboard_main.rdl. Click Run to run the report. You will see the following report:

I ran this just after a restart of SQL Server, so there isn’t a lot of information in there yet. However, by the end of the day this report will prove very valuable. The part I want to concentrate on, though, is the link in the bottom left that says “Missing Indexes”. Click on that to run the Missing Indexes report:

(click for full size)

The columns you should concentrate on here (or at least the ones I concentrate on) are Average User Impact and Proposed Index. The Average User Impact column tells us how much of a benefit the user would see if you apply the proposed index. The higher the number, the more you should consider applying the index. The Proposed Index column actually contains the code you need to create the index. What I do here is I sort this report by Average User Impact, then export the report to Excel (right click anywhere in the white space and click “Export” then “Excel”).

This allows me to select the text in the Proposed Index column, copy it, and then paste the code and run it in Management Studio.

I have seen some great benefits with the Performance Dashboard. In one case we had a query that once took nearly 10 minutes to run return results in under a minute after we applied some of the Proposed Indexes.

As always, feel free to leave any questions in the comments of this post. I’m also interested to hear any success stories for those who have also used these reports.

I thought today would be a good time to share a SQL Server tip I use frequently at work. The data I work with and report on is often presented in a crosstab fashion. This is fairly easy to do in Microsoft Access, as Access has a built in Crosstab Wizard. T-SQL, the query language used in SQL Server, however, has no such functionality. You have to code it yourself.

For this example I’m going to use the data found at the Baseball Archive, which has an extensive collection of Major League Baseball statistics from the 1800’s all the way to last season available for free personal use. The data they provide is contained in an Access data, but I went ahead and imported it to SQL Server. For this example I’m using two tables: the Fielding table to obtain Position information and the Batting table for the number of Home Runs.

Let’s say you wanted to know how many Home Runs were hit per position per year from 2000 to 2003. You could write the query like this (I filtered out the Pitcher statistics because who cares about Pitcher’s hitting stats):

Query 1

select Fielding.POS, sum(hr) as hr, batting.yearid
from dbo.Fielding inner join batting on Fielding.PlayerID = batting.PlayerID
where (batting.yearID > ‘1999′ and batting.yearID < ‘2004′ ) and Fielding.pos <> ‘p’
group by Fielding.pos, batting.yearid
order by batting.yearid

Query 1 Results

This does give you the number of Home Runs hit per year by Position. However, it’s hard to examine two years of numbers without scrolling. Now, let’s look at the results using a crosstab query.

Query 2

/* Cross Tab */
select Fielding.Pos,
sum( case
when batting.yearid = ‘2000′ then (batting.hr)
end) as ‘2000 - Home Runs’,
sum( case
when batting.yearid = ‘2001′ then (batting.hr)
end) as ‘2001 - Home Runs’,
sum( case
when batting.yearid = ‘2002′ then (batting.hr)
end) as ‘2002 - Home Runs’,
sum( case
when batting.yearid = ‘2003′ then (batting.hr)
end) as ‘2003 - Home Runs’
from dbo.Fielding inner join batting on Fielding.PlayerID = batting.PlayerID
where (batting.yearID > ‘1999′ and batting.yearID < ‘2004′ ) and Fielding.pos <> ‘p’
group by Fielding.pos
order by Fielding.pos

Query 2 Results

Much better, right? Here you can see the trends of what is happening at each position per year.

The key to the crosstab query above is the case statement. What the code is going is testing the year and if it is equal to ‘2000′ is summing batting.hr. That’s all there is to it. The last thing I would do is take this code and save it as a stored procedure, so it can be used in you .Net application or wherever.
Improvements:

• Send the year to the query as a parameter so this query can be used for any range of years.
• Number of years could even be a parameter, making this even more “dynamic”
• ?

Any questions? Let me know if you can think of any other improvements you would add to the above code. Also, let me know if you would like to see the above code with the improvements I mentioned.

Microsoft announced yesterday that SQL Server 2008 will launch on February 27, 2008. For those of you who are still waiting to upgrade to SQL Server 2005, you may want to wait a little longer.