Whew!  So I've been super busy the past week and a half upgrading our production server from SQL 2000 to SQL 2008 and haven't had time for any new posts.  Finally starting to see everything calm down again though and running smoother than ever before.  I had hoped to do a comparison of 2000 and 2008 disk activity pre and post compression, but due to the number of "emergencies" in the week prior to the changeover and the number of times our plans changed with reconfiguring raid arrays etc. I never had an opportunity to get good solid numbers.  I can report a very rough estimate though. 

The compression on my objects is distributed as:

Type # None 2042 Row 107 Page 549  

Our database went from 75g to about 30g after a good amount of page compression.  Our CPU Usage hasn't gone up that I've noticed.  I haven't seen it break 30% total utilization yet.  If it did go up, it didn't go up...

Read More »

This post is a T-SQL Tuesday Entry, hosted this week by Aaron Nelson on the topic of “Reporting”.  (It got a little long.  Ordinarily I’d have broken this up into a series and fleshed out individual pieces a bit better, but this touches on most of the general points)

I like babysitter reports.  What is a "babysitter" report?  It's a report that you schedule to run on a recurring basis that checks things for you.  I call them babysitter reports because they can monitor things without you having to worry about it.  Every environment has different things that they need to look for.  Maybe a certain value found its way into a certain table and you need to take action because of it.  Maybe a certain query is on a rampage again and you need to kill it.  There are all kinds of things that you know you should keep an eye on that you don't always remember to do.  Instead of putting that burden on...

Read More »

Ok, continuing from my post last night about the ‘Run as Administrator’ bug in SSMS, here’s the other half of what I had to do to get Reporting Services running on Windows 7 Home Premium.  The whole issue was that the account I was using did not have permissions to do anything to the reporting services server.  I could not add permissions no matter what I tried.  Running things as an administrator got me a bit farther, but not all the way there.

The root of the problem appears to be that even though you can’t see it, there IS an ‘Administrator’ account, and that is what has permissions by default.  Even though the account I was logged in as was *an* administrator, as far as Reporting Services was concerned, I didn’t have access. 

So how do you fix it?

First, you need to enable the local admin account.

...

Read More »

Was attempting to write a blog post for T-SQL Tuesday this evening and was making good progress until I hit a 2 hour roadblock.  As far as I can tell, this is a combined issue between Windows 7, SSMS, SQL Server 2008 and reporting Services.  Basically, unless you choose 'Run as Administrator' for SSMS, you cannot: Add new roles, edit existing roles, etc. in Reporting services inside of SSMS, regardless of the permissions of the user you connect as.  Highly frustrating and very hard to find a solution for online.

I could not deploy reports.  I could not add or edit roles.  I could not do anything to fix the problem until I ran the tool as an administrator.  Why that has anything to do with permissions inside of Reporting services I don't know, but it definitively does.  I found it extremely hard to believe that this was a legitimate reason and went back and forth between administrator / normal 3 times and it changed my ability to modify things each time.  The account I’m logged into is a local windows admin,...

Read More »

The April meeting went well.  I had hoped that the prospect of a couple of Technet subscriptions would increase attendance a bit for this meeting, but it doesn't seem to have done so.  On the bright side, that meant my competition for them was less and I managed to walk out with one at the end of the night.  We had our usual group of about 8 or 9 people.

Herve Rogerro (Blog | Twitter) gave a great presentation on indexes, join types and some common things that catch people when they try to analyze performance.  Herve is obviously very comfortable speaking in front of a crowd (Or if he's not he does a very good job of pretending to be).  Here is what one of the members had to say about it:

I really enjoyed Herve’s presentation on Performance and Best Practices for Indexing in SQL Server last night.  He started with the basics concepts of covering/clustered/non-clustered/unique...

Read More »

Overall, not too bad for the first quarter, but definitely some areas I need to pick up the slack on.



Community Involvement

New Posts on SSC Forums:  Got a bit behind on this one as I really wasn’t posting until about a month ago.  I’ve found that with the forums, I either post more or less daily, or I get overwhelmed with all the posts I haven’t read and it’s hard for me to post at all.  I still think I’ll be able to pull this off by the end of the year as long as I don’t take another 6 month hiatus.

Article for SSC:  I’ve written my first article...

Read More »

A reminder to anyone in the Brevard County Florida area:



The SCSUG monthly meeting is at 6:30 on Thursday, April 8th at the SCCU HQ featuring Herve Roggero ( Blog | Twitter).  Herve will discuss SQL Server performance and best practices on indexing, the new indexing capabilities of SQL Server 2008, execution plans, and how JOIN operators are selected by the SQL Server Engine.

 

...

Read More »

Totally pointless, but fun.  Run it to decode the message.

DECLARE @Message varchar(20)
SET @Message = '????????????'

DECLARE @Decode Table(
DSeq tinyint,
DKey smallint
)

INSERT INTO @Decode(DSeq, DKey)
VALUES(1,9),(2,2),(3,17),(4,17),(5,26),(6,6),(7,2),(8,20),(9,21),(10,6),(11,19)

; WITH
Decoder AS (
    SELECT D.DSeq N,
    CHAR( D.DKey + ASCII(SUBSTRING(@Message,D.DSeq,1)) ) DV
    FROM @Decode D),
Conc (S) AS (
    SELECT DV + ''
    FROM Decoder D
    ORDER BY D.N
    FOR XML PATH(''))

SELECT STUFF(S,6,0,' ') [Surprise]
FROM Conc

When you ask a question on the forums, you'll often get responses that certain ways of doing things are inefficient or that you should be doing things a different way.  While that is true, there's a flip side to this.  In a perfect world, you would always do things the most efficient way regardless of the cost.  In the real world, you've got a lot of things to do and not always enough time to do them in.  Compromises, unfortunately, must be made.  Sometimes you go ahead and use that cursor or while loop on a report that only gets run once a month to save yourself from coming up with a much more complicated set based method.  Sometimes you use a temp table you could have done without.  What needs to be done to keep things moving isn't always the best solution, but it is often the best solution that you have time for.

For most people, this is where that extra effort will come in.  While you might have to make the compromise to get things done, knowing how to implement it the more efficient way (or exploring...

Read More »

I started blogging a few months ago and since I started writing my own blog, I've been a much bigger follower of other technical blogs.  I now follow over 25 SQL blogs and add more on a regular basis. Some of the blogs I follow are updated every day and usually feature shorter articles.  Some of them are updated much less frequently and feature much longer and more in depth articles.  Most are somewhere in between.  Lately, I've been doing a lot of thinking about what warrants a blog post.   For me, figuring out what is worth writing about is almost as time consuming as actually writing it.

For example, I was working with data compression the other day and noticed several things that changed my decisions on some changes I was going to make in my environment.  One of these hinged around a clustered index key compressing extremely well with page level compression so that it was only slightly bigger than a clustered key that was much narrower.  The problem with this is, I can't really explain why.  I can...

Read More »

Whew!  So I've been super busy the past week and a half upgrading our production server from SQL 2000 to SQL 2008 and haven't had time for any new posts.  Finally starting to see everything calm down again though and running smoother than ever before.  I had hoped to do a comparison of 2000 and 2008 disk activity pre and post compression, but due to the number of "emergencies" in the week prior to the changeover and the number of times our plans changed with reconfiguring raid arrays etc. I never had an opportunity to get good solid numbers.  I can report a very rough estimate though. 

The compression on my objects is distributed as:

Type # None 2042 Row 107 Page 549  

Our database went from 75g to about 30g after a good amount of page compression.  Our CPU Usage hasn't gone up that I've noticed.  I haven't seen it break 30% total utilization yet.  If it did go up, it didn't go up...

Read More »

This post is a T-SQL Tuesday Entry, hosted this week by Aaron Nelson on the topic of “Reporting”.  (It got a little long.  Ordinarily I’d have broken this up into a series and fleshed out individual pieces a bit better, but this touches on most of the general points)

I like babysitter reports.  What is a "babysitter" report?  It's a report that you schedule to run on a recurring basis that checks things for you.  I call them babysitter reports because they can monitor things without you having to worry about it.  Every environment has different things that they need to look for.  Maybe a certain value found its way into a certain table and you need to take action because of it.  Maybe a certain query is on a rampage again and you need to kill it.  There are all kinds of things that you know you should keep an eye on that you don't always remember to do.  Instead of putting that burden on...

Read More »

Ok, continuing from my post last night about the ‘Run as Administrator’ bug in SSMS, here’s the other half of what I had to do to get Reporting Services running on Windows 7 Home Premium.  The whole issue was that the account I was using did not have permissions to do anything to the reporting services server.  I could not add permissions no matter what I tried.  Running things as an administrator got me a bit farther, but not all the way there.

The root of the problem appears to be that even though you can’t see it, there IS an ‘Administrator’ account, and that is what has permissions by default.  Even though the account I was logged in as was *an* administrator, as far as Reporting Services was concerned, I didn’t have access. 

So how do you fix it?

First, you need to enable the local admin account.

...

Read More »

Was attempting to write a blog post for T-SQL Tuesday this evening and was making good progress until I hit a 2 hour roadblock.  As far as I can tell, this is a combined issue between Windows 7, SSMS, SQL Server 2008 and reporting Services.  Basically, unless you choose 'Run as Administrator' for SSMS, you cannot: Add new roles, edit existing roles, etc. in Reporting services inside of SSMS, regardless of the permissions of the user you connect as.  Highly frustrating and very hard to find a solution for online.

I could not deploy reports.  I could not add or edit roles.  I could not do anything to fix the problem until I ran the tool as an administrator.  Why that has anything to do with permissions inside of Reporting services I don't know, but it definitively does.  I found it extremely hard to believe that this was a legitimate reason and went back and forth between administrator / normal 3 times and it changed my ability to modify things each time.  The account I’m logged into is a local windows admin,...

Read More »

The April meeting went well.  I had hoped that the prospect of a couple of Technet subscriptions would increase attendance a bit for this meeting, but it doesn't seem to have done so.  On the bright side, that meant my competition for them was less and I managed to walk out with one at the end of the night.  We had our usual group of about 8 or 9 people.

Herve Rogerro (Blog | Twitter) gave a great presentation on indexes, join types and some common things that catch people when they try to analyze performance.  Herve is obviously very comfortable speaking in front of a crowd (Or if he's not he does a very good job of pretending to be).  Here is what one of the members had to say about it:

I really enjoyed Herve’s presentation on Performance and Best Practices for Indexing in SQL Server last night.  He started with the basics concepts of covering/clustered/non-clustered/unique...

Read More »

Overall, not too bad for the first quarter, but definitely some areas I need to pick up the slack on.



Community Involvement

New Posts on SSC Forums:  Got a bit behind on this one as I really wasn’t posting until about a month ago.  I’ve found that with the forums, I either post more or less daily, or I get overwhelmed with all the posts I haven’t read and it’s hard for me to post at all.  I still think I’ll be able to pull this off by the end of the year as long as I don’t take another 6 month hiatus.

Article for SSC:  I’ve written my first article...

Read More »

A reminder to anyone in the Brevard County Florida area:



The SCSUG monthly meeting is at 6:30 on Thursday, April 8th at the SCCU HQ featuring Herve Roggero ( Blog | Twitter).  Herve will discuss SQL Server performance and best practices on indexing, the new indexing capabilities of SQL Server 2008, execution plans, and how JOIN operators are selected by the SQL Server Engine.

 

...

Read More »

Totally pointless, but fun.  Run it to decode the message.

DECLARE @Message varchar(20)
SET @Message = '????????????'

DECLARE @Decode Table(
DSeq tinyint,
DKey smallint
)

INSERT INTO @Decode(DSeq, DKey)
VALUES(1,9),(2,2),(3,17),(4,17),(5,26),(6,6),(7,2),(8,20),(9,21),(10,6),(11,19)

; WITH
Decoder AS (
    SELECT D.DSeq N,
    CHAR( D.DKey + ASCII(SUBSTRING(@Message,D.DSeq,1)) ) DV
    FROM @Decode D),
Conc (S) AS (
    SELECT DV + ''
    FROM Decoder D
    ORDER BY D.N
    FOR XML PATH(''))

SELECT STUFF(S,6,0,' ') [Surprise]
FROM Conc

When you ask a question on the forums, you'll often get responses that certain ways of doing things are inefficient or that you should be doing things a different way.  While that is true, there's a flip side to this.  In a perfect world, you would always do things the most efficient way regardless of the cost.  In the real world, you've got a lot of things to do and not always enough time to do them in.  Compromises, unfortunately, must be made.  Sometimes you go ahead and use that cursor or while loop on a report that only gets run once a month to save yourself from coming up with a much more complicated set based method.  Sometimes you use a temp table you could have done without.  What needs to be done to keep things moving isn't always the best solution, but it is often the best solution that you have time for.

For most people, this is where that extra effort will come in.  While you might have to make the compromise to get things done, knowing how to implement it the more efficient way (or exploring...

Read More »

I started blogging a few months ago and since I started writing my own blog, I've been a much bigger follower of other technical blogs.  I now follow over 25 SQL blogs and add more on a regular basis. Some of the blogs I follow are updated every day and usually feature shorter articles.  Some of them are updated much less frequently and feature much longer and more in depth articles.  Most are somewhere in between.  Lately, I've been doing a lot of thinking about what warrants a blog post.   For me, figuring out what is worth writing about is almost as time consuming as actually writing it.

For example, I was working with data compression the other day and noticed several things that changed my decisions on some changes I was going to make in my environment.  One of these hinged around a clustered index key compressing extremely well with page level compression so that it was only slightly bigger than a clustered key that was much narrower.  The problem with this is, I can't really explain why.  I can...

Read More »