Archive for November, 2008

As I wrap up the work that I wanted to get done this week I was thinking about all the things that I needed to do before I knocked off for the day and started cooking my favorite dish a homemade batch of Shredded barbeque beef. It got me to thinking how many holidays I have had ruined because servers just did not get the understanding that I was not supposed to be working that day.

So the thought for the day…

Have a great Thanksgiving, eat more then you should enjoy the games or parades and make sure that you relax while you have the time. But before you leave the office for the day, check your backups. Make sure they are there and secure, make sure you have the numbers of everyone you need just in case something does happen. I just finished doing a review on SSWUG that I think will be posted in the next couple weeks on backup compression software. I got a huge kick out of the hyperbac tool and I think it may change the way you look at backup compression. As soon as the review is posted I will make sure I put a link up here.

SQL Server 2008 IntelliSence

Posted: November 24, 2008 in Uncategorized

 

Pre release of SQL Server 2008 I did a number of presentations on the new features that were going to be in SQL Server 2008. At the time that I was working on these presentations I was working of any number of the CPT’s that were out. The problem with working with CPT’s is that features sometimes change and most of the time they do. Here is where I have to mention some of the items that I presented on were not correct. One of these is a feature I thought I would love, but with my use to this point I am starting too loath. Keep in mind I have not worked with the production release but for a short period of time at this point so it could just end up being something that I need to get more use to. This feature is called IntelliSence.

What is it?

According to Books on Line “IntelliSense in the Database Engine Query Editor supports the following syntax elements when they are used in one of the supported Transact-SQL statements…”

In English or maybe a little easier to understand, it bring some functionality from Visual studio and Word into the query window. Let’s say that you working on a query and you start typing

Select * from sys

But that is as far as you get before you get the drop down to help you complete the statement. It looks like this

 

Now this query is only there to show you what it is but you can see from the drop down that I can now select databases, objects and so on. The other feature that you may notice is the Word Famous Red underlining. This is to let you know something is not like it should be, I see this a lot as I type.

Why is it bugging me?

Well I have reached a point where I have been working on some queries that have multiple columns in the table that have the same first few letters. For some reason I am having a difficult time telling it I want “Moviereview” instead of “MovieName”
I have mouse clicked on it and it seems to always default back to the first one it pick. Again this may be just the way that I am using it and I need to get better at doing it. But I have even arrowed down and hit enter and it defaults back to the name I don’t want. I will post more as I figure out the fix.

If you want to turn it off.

I am not going to turn this off until I am sure that it’s not me doing something wrong. But if you are having the same issues that I am and you just want to turn the feature off simply in your SQL Server Management Studio select Tools – Options once you get the Options drill down you will find this under Text Editor – Transact-SQL – IntelliSense.

Remove the check mark and you are back to what you are use to.

 

More SQL Put Downs

Posted: November 21, 2008 in Uncategorized

 

I am not sure where we think of this stuff. But hey here it is starting up again…

  • Yo momma’s so dumb, when I said “DROP VIEW” she thought I was canceling her favorite TV show! –
  • Yo Mamma’s so dumb when I said create view she thought I was hitting on her. – Chris Shaw
  • Yo momma so dumb she thought SPID was the past tense of spit. – Chris Roland
  • yo mama’s too poor to BEGIN TRANSACTION. – Blue_fenix
  • Yo momma is so slow, she thought Gemini meant “Jim and I” – Joe Webb
  • Yo mama so ugly she walked by a mirrored database and it broke! – SQLAgentMan
  • Yo mamma is so fat her rows overflow. – Jason Massie
  • Yo momma’s so dumb, she was looking for the prequel! – Denis Gobo
  • Yo Momma’s so red neck she thought RollBack was parking the house… – Chris Shaw

If you have some more tweet them to #sqlpass or feel free to post them here.

Call for Speakers

Posted: November 20, 2008 in Uncategorized

It was not 2 weeks ago and we were knee deep in some of the best technical information that is available on the web. Sessions were delivered on SQL Server, Share Point, .NET and Business Intelligence it was the SSWUG VConference. As we are still in the on-demand period for this conference I find myself already getting ready for the next one. We are planning on how we can make it bigger and better, with all new content. The next conference is scheduled for April at this point.

This is where you come in. There is an open call for speakers, all you need to do is go to the application and fill it out, tell us all about who you are what you would like to speak about. All session levels are welcome. A few things to keep in mind…

  • We cover flight and hotel to the studio as long as you are doing at least 3 sessions.
  • We will pay you for your sessions and there are added opportunities to make some extra money by doing additional sessions and webcasts.
  • This is a great opportunity to stay connected to the community.

If you have any questions drop me a note here. I would be more than happy to answer as many questions as I can.

 

In the last few versions of SQL Server Microsoft has given the DBA a new way to look at what is going on inside SQL Server for one of the SQL on Call clients. The information that is contained in these views can at times be a little difficult to read or actually relate to your data. However there are many times when a common question and a valid question could not be answered. Let me give you an example.

A database developer is chasing down a performance issue on a table that he or she has. It appears that inserts are taking a long time and they have reviewed their code and cannot understand why. The developer did notice that there appear to be a large number of indexes on the table and are curious if they are all being used.

The Question that needs to be answered here are there indexes that are not being used that may be slowing down the insert statements. If these rows that are being inserted are causing index pages to be written and split when the index is not even being used could be the cause of this performance problem. This is an actual problem that I was trying to resolve not to long ago. One of the ways that I can answer that question is by using the Dynamic Managed Views.

For the rest of this post we will use the above example. Please keep in mind that this code that I was using was to solve a specific problem that I was having. As I work my way through the DMV’s I find more and more information all the time. Reason I mention this is that I join my DMV back to the sysobjects table. This is the way I use to get information about my database but as I move around the DMV’s I am adjusting the way I look at and for information.

The first goal was to see how many indexes were not used at all. This needs to be run in the context of the database that you are reviewing.

– This will give you a list of Indexes that are not being used at all

 

Select
name, i.*

from
sys.dm_db_index_usage_stats i

    Join sysobjects o on i.object_id
= o.id

Where user_lookups = 0

and user_seeks = 0

and user_scans = 0

and system_lookups = 0

and system_seeks = 0

and system_scans = 0

After some time reviewing this I wanted to check to see how many of these indexes were not being used very often.

– This will give you a list of Indexes that are not being used very little

 

Select
name, i.*

from
sys.dm_db_index_usage_stats i

    Join sysobjects o on i.object_id
= o.id

Where user_lookups < 100

and user_seeks < 100

and user_scans < 100

and system_lookups < 100

and system_seeks < 100

and system_scans < 100

and
object_id
> 1000

 

With a little bit more discover I was able to start expanding my Query.

 

– This will give you a list of Indexes with the Tablename and Index name

– of the indexes that are not used very often

 

Select
Distinct o.name,si.name

from
sys.dm_db_index_usage_stats i

    Join sysobjects o on i.object_id
= o.id

    Join sysindexes si on i.object_id
= si.id

Where user_lookups < 100

and user_seeks < 100

and user_scans < 100

and system_lookups < 100

and system_seeks < 100

and system_scans < 100

and
object_id
> 1000

and
(o.name is
not
null
and si.name is
not
null)

and si.name not
like
‘PK_%’

and si.name not
like
‘_WA%’

and si.name not
like
‘_hind’

 

The way that many of the DMV’s are used is based upon the last time the system was restarted. So in these examples that I have posted if you were to run these seconds after a restart and before you have allowed traffic on the server then you may be in a situation where you would be getting false results. The best way to avoid this is to run this after your server has been on-line for some time. This then becomes a task on if the developers want to review the indexes to see if they can start to remove these unwanted indexes.

Last Day At Connections

Posted: November 13, 2008 in Uncategorized

Being here at SQL Connections the time always moves pretty fast. There are blocks where you know you would like to go to all the sessions but you just can not make it there. The only option that you are left with is picking up one of the CD’s and going over the slides. Ted Malone had a great session yesterday where the room was pack, and I am sure that Matthew Roche had the same.

I had a chance to talk with Andrew Kelly about the Marine Corps and it was nice to meet a fellow former Marine. My sessions went well I felt and the audience participated well, there were some video guys walking around and it may even end up on some tape somewhere. I would be interested in seeing if that happens. If it does I will post it here. I did have someone that snapped a few photos of me speaking, so I figured I would put one up here on my blog. I know that I talk a lot with my hands but I was surprised at how many hand gestures I was making.

 

Last night we went to an event with SQL Sentry and it was great to have the chance to talk with them about Performance Advisor and how it has helped me with some of my clients.

Today I sat through a session with Jeremy Lowell and pick up some tips that I can start using. Well I am off to the airport and I am glad that next week should be a little quieter then this week. The travel takes a lot out of me, but I have to admit that I like to know that I may have helped someone in either my session or on a sidebar.

Dev Connections

Posted: November 12, 2008 in Uncategorized

Today was a big day at connections and to think that by this time tomorrow night we will be over half way done. When Connections heads into Las Vegas you are sure that your days are going to be long. I started my day be sitting though a Key Note by Scott Guthrie. I did not realize how much Microsoft had released until Mr. Guthrie went through the list. The rest of the day was filled with sessions all by Microsoft speakers. I like the way that Connections has all the speakers from Microsoft schedule for one day. It gives us a good look at where they see the products going. There was a great session by Goldie Chaudhuri on spatial data. But the big buzz I am hearing is how everyone can not believe how easy the new report builder is.

After the last session the exhibit hall opened with in my opinion the best food yet. Everyone filled up on Pizza and drinks were all around. Jeremy Lowell and myself went from there over to a new event that was being hosted by Microsoft where we mingled with other speakers and some vendors, the subject matter was all about computing green. Green the new keyword that is really just a way that we can plan on saving on resources with many times the side benefit of a cost savings as well.

It’s great to see all the speakers and old friends that we get to see twice a year. But I have to keep my head about me with 2 weeks in a row of conferences it makes it difficult to keep the energy level up. I speak tomorrow and will be presenting on Lessons learned from SOX Audits and My first 30 days as a DBA on a new Job. So it is off to bed with me and I hope to post more tomorrow. I already have a great meeting set up and a dinner with SQL Sentry to talk about what we would like to see in their products.