Well, it’s been 10 days since I started to play with parallelism on this server. I have gotten the self blocking to stop. My CPU utilization has stabalized and my phone is not ringing. So I think that the issues is mitigated. I finally settled on Max Degrees of Parallelism of 2 and the Cost Threshold for Parallelism of 500. I also have the developers looking at the code to ensure all their joins are nolock, they tell me they should be, but I am asking nicely to them to verify. So I’m moving on to other issues unless this one pops back up.
Archive for the ‘MSSQL’ Category
Update: Degrees of Parallelism
Friday, February 13th, 2009
Things You Know Now
Thursday, February 12th, 2009Denny Cherry tagged me in a blog thread with a question asking “What do you wish you knew when you started?”. So here you go. As you know I am still relatively new to the SQL profession so I’m still figuring out what I don’t know. I seem to find several every day, so I’m going to step back to my IT career in general. I think some will continue to ring true.
Don’t be afraid of new technology.
For a long time I had a love/hate relationship with new technology. I love getting into it, learning about it, and deploying it. It’s quite a rush to be the first one to attain knowledge and being the go-to person for something cutting edge. But I also had a fear of new technology. “Will this technology and automation make me obsolete?” A few years ago blades started to trickle into the datacenters. This was one of those times. I can take a blade out of a box and slide it into a chassis, boot from the SAN image, and a failed server is fixed. Wow, they don’t need someone of my salary to do that. No more messing with cables, world wide names, KVM swaps. But I found just the opposite is true. They needed someone of my salary level to get it working and stable. Hey guess what, others can do the break/fix, I get to do more fun stuff. Linux and virtualization at that time. I’m wondering how the cloud will fit this model.
The manual is not the enemy.
I don’t know why I do it, I guess it’s a macho thing. I would never read the manuals, in public. But somehow the CD would slip into my backpack when we were having a problem and somehow magically I would have a brilliant idea over night that would solve the problem. If I would have just grabbed the CD and gone to my desk I could have saved hours of “poke and hope” troubleshooting. Now I have no problem saying “lets see if we are doing this the way the people who built it wants it done.” I always hate it after struggling for hours to get something working, I look at the manual and in big black print I read something like this “before installing the flux capacitor make sure the knueter valve is set to 42% or the capacitor will not initially charge” and I think “crap I cranked that puppy all the way up, no wonder it didn’t work.”
See the big picture
I used to just worry about my piece of the puzzle. Give me a task and a due date and get the heck out of my way. But I learned to take a step back and see how my piece fit into the whole puzzle and look to see how my knowledge and experience can assist the project as a whole. What is hard for some may be easy for me, or I have a different approach that make it easier for everyone. Ask questions respectively, which will lead into my next topic.
Watch my mouth
This one got me into real trouble. I was in a meeting and a manager was describing a problem and had some pronoun problems. “It” referred to 4 or 5 different things. I couldn’t follow the description with a GPS and a big orange “you are here” arrow. Not thinking about how it would sound, I asked if they could explain it again using a few less pronouns. That was offensive to that manager, and to my manager who wasn’t even in the meeting to hear it. I do believe there is a note in my personnel file about this one. Asking better questions in private would have been a much better path. That manager and I are back to being friends, but that experience is never out of my mind.
So now it’s my turn to pass this along. Let see, who hasn’t been hit…. How about
Jimmy May (@aspiringgeek on Twitter)
Kevin Hill (@kevin3NF on Twitter)
Starting up database ‘SomeDbName’
Wednesday, February 4th, 2009I love it when I learn something within a half hour of getting to work. I had an unrelated alert on a vendor supplied database server. As I was investigating that problem I came across two things. One was funny, one I actually learned something. First the funny: they had named the sysadmin account ‘God’. Wasn’t that from the terrifically awesome, how can I live without seeing that one, movie ‘Hackers?’ But now onto what I learned. I looked at the SQL Server Log and I had hundreds of entries that said “Starting up database ‘DbName’” These were appearing several time a second. I’m thinking. This has got to be a waste of precious CPU. I know the server site at 10% utilized even when the two people we bought it for are using it, but that’s beside the point. Its CPU darn it. Well, I have to admit that message meant nothing to me. So I asked my favorite co-worker, Mr. Google, what was up. He told me the server is set to Auto Close. I looked at the options and he, of course, was right. Auto shrink was enabled also. So, I fixed those two issues and now the database server is chugging along at 9.98% utilized. It’s only 7:44 am and I believe my work is done here. Time to twitter for the next few hours till quitting time. Crap, it only works that way in movies. Another database needs my care.
Degrees of Parallelism
Tuesday, February 3rd, 2009If you are expecting a deep, well researched, thorough explanation of degrees of parallelism and cost threshold for parallelism you have come to the wrong place. I’m sorry. Hit the back button and choose a different link. O.K., now that I’m alone again, I can get into my issue. I have a vendor application, which will remain nameless, that is having some problems. I have noticed by watching activity monitor that this database really chews up CPU. When this app is quiet, I see CPU in the teens. When it starts running I see 50% with 70% spikes. But what has caught my eye, using SSMS 2008, is how much this app self blocks. I don’t know if that is a real term, but I like it. What I am talking about is one SPID that spawns several threads with the same SPID and those threads start blocking other threads on the same SPID. As soon as I start seeing this I see CPU start climbing. I also see waiting process start to grow. It doesn’t take too long until I hear my phone ring. The vendor has told me I can kill off these processes without harming the application. So I’ll kill a few of them and get CPU back down to 30% – 40% and everyone is happy. So I start wondering. If I can decrease the number of threads that run at one time, maybe I can decrease the blocking and resolve this issue. I read a post of someone who changed max degrees of parallelism from 0 to 1 and his problem went away. That made perfect since to me: every process can have one CPU at a time. But that also bothered me. Microsoft put parallelism into the product because it helps with performance. Turning it off can’t possibly make things faster. There has to be a balance. Increase the threshold, lower the number of processors there has to be a sweat spot. But how to measure it. That is my problem. HMMM…… There must be a objective way to measure the application speed and make changes, watch the CPU, and the application speed.
Speaking of SSMS 2008. I really like the activity monitor. This seems to really give a lot more information. The data file I/O section sorted by response time is very interesting to watch and I’m sure I will be using it in discussions with my SAN team in the future. I just wish I could figure out how to freeze the processes section. The refresh gets a little annoying to me. But all in all I really like it.
Well I’ll spend some more time on this tomorrow and maybe have some solutions to my problem with this application. Wish me luck.
Developer can not create views
Wednesday, January 28th, 2009I received a e-mail a few days ago from one of our developers informing me that he is unable to create views on one of our SQL Server 2005 databases. He also told me he had another developer try and she also could not create views. Interesting, I thought, so out to my favorite co-worker – Google. My buddy Google quickly pointed me to this MSDN post. This explained to me that when a user gains rights by Active Directory group membership they are not given a default schema. Say what? Yes, that’s right, because multiple AD groups could have rights on a database, and a user could be a member of multiple AD groups it could get confusing. So any statement that requires a schema will fail with some form of the following error message.
Property DefaultSchema is not available for Database ‘[<database>]‘. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (SQLEditors)
So the first solution to the problem I found was to create a login for the user on the database server, give him public rights to the database in question, then revoke connection rights to that user. What a bunch of work, I’m much to lazy to do that kind of work on every database for every developer in our organization. So I called upon another friend, I believe he has run into every possible SQL issue and lived to tell about it. That, of course, is Denny Cherry. He replied with a simple solution. Have the developer prepend (I know, there are discussions about if this is a word or not. But I like it so I’ll use it. I have never really been bothered by that. Originally there were no words and people started using them and they became words. Maybe….) the schema. to the view name during the creation. I love it, it let’s the developers resolve the problem.
One New Years Resolution Down….
Friday, January 16th, 2009After being tagged to blog my Professional New Years Resolutions late last year, I made getting certified as my first one. Today I bit the bullet and took the 70-431 test. I took about an hour to complete the 35 trivia (multiple choice) and 12 simulations. I was a little shocked when the screen displayed 1000/1000 for my score. The family and I celebrated big time. We went to Moe’s Southwest Grill and then went to the mall so the kid could use the Christmas money that was burning a hole in their pocket. So I’m going to take the rest of the month off, then start studying for the next one. Maybe the 70-432…
Finding databases that have changed to Simple Recovery Mode
Friday, January 2nd, 2009As I am beginning our journey into SQL 2008, I am taking this time to go through all our scripts and try to resolve some of the issues I get alerted on that are due to how we do things. One of the things we do is programatically list all the databases, determine which are prod, test, & system. Then the Prod databases have xlog backups and the others do not. Occasionally, some of our prod databases will change from Full Recovery Mode to Simple Recovery Mode. How I find out about this is that the Xlog backup job will fail. The first database that should have been in Full but was in Simple would fail the whole job and transaction logs would not be backed up. Failing jobs is a horrible way to find out that a recovery mode changed. So I re-wrote our backup job to query sys.databases and do xlog backups to any database in full recovery mode. No more xlog failures for wrong setting. Life is good. But wait, what if a database does change modes how will I know? Sure the xlogs of databases that are in full recovery mode backed up, but that may not be good enough. So I sat back down and started wrote an new script. This script looks at yesterday’s list of Simple Recovery Mode servers and compares it to Today’s list. If there is a new database in Simple recovery mode it sends me an e-mail to investigate it. If there is no change, it just ends. Here’s the code.
declare @NewSimple int, @subject varchar(256)
select name into maintenance..backupsimple from sys.databases where recovery_model = 3
SELECT name into ##backupchange
FROM
(
SELECT name
FROM maintenance..backupsimple
EXCEPT
SELECT name
FROM maintenance..backupsimpleold
) tmp
GROUP BY name
HAVING COUNT(*) = 1
ORDER BY name
select @NewSimple = COUNT(name) from ##backupchange
if @NewSimple >0
BEGIN
select @subject = ‘New Simple Mode Databases on server ‘ + replace(@@servername, ‘\’, ‘_’)
exec msdb..sp_send_dbmail
@profile_name = ‘SQL Server’,
@recipients = ‘<My E-mail address>’,
@importance = N’HIGH’,
@subject = @subject,
@body = ‘Please verify the following databases should be in Simple Recovery Mode. They were not in Simple Recovery mode yesterday. Remember to perform a full backup if you change the model to full recovery mode:’,
@query = ‘select name from ##backupchange’
END
drop table maintenance..backupsimpleold
select name into maintenance..backupsimpleold from maintenance..backupsimple
drop table maintenance..backupsimple
drop table ##backupchange
I have this running once a day for now. Maybe in the future it will need to run more often.
Getdate() Formatting
Tuesday, December 30th, 2008I get the occasional question from our Oracle DBAs on how do you do this in MSSQL. Usually I have little to no idea, so I do what all new DBAs do: Look it up. Well, this situation came up today on how can I write a procedure that will query the table and give me all the enteries for today. The column the date is stored in is varchar in this format 11/14/2008. I found the answer is select convert(varchar,getdate(),101) So I thought I’d add the sytle ID numbers for future reference.
|
Style ID |
Style Type |
|---|---|
| 0 or 100 | mon dd yyyy hh:miAM (or PM) |
| 101 | mm/dd/yyyy |
| 102 | yyyy.mm.dd |
| 103 | dd/mm/yyyy |
| 104 | dd.mm.yyyy |
| 105 | dd-mm-yyyy |
| 106 | dd mon yyyy |
| 107 | Mon dd, yyyy |
| 108 | hh:mm:ss |
| 9 or 109 | mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 110 | mm-dd-yyyy |
| 111 | yyyy/mm/dd |
| 112 | yyyymmdd |
| 13 or 113 | dd mon yyyy hh:mm:ss:mmm(24h) |
| 114 | hh:mi:ss:mmm(24h) |
| 20 or 120 | yyyy-mm-dd hh:mi:ss(24h) |
| 21 or 121 | yyyy-mm-dd hh:mi:ss.mmm(24h) |
| 126 | yyyy-mm-dd Thh:mm:ss.mmm(no spaces) |
| 130 | dd mon yyyy hh:mi:ss:mmmAM |
| 131 | dd/mm/yyyy hh:mi:ss:mmmAM |
