Archive for August, 2009

SQL Server State of the Union

Posted: August 27, 2009 in Uncategorized

 

A few days ago Tom Roush sent this message into the SSWUG List server. I loved the idea so I thought I would post about it. What would I have answered…

 

Awhile back Chris Shaw made an interesting comment during a presentation about wanting anonymity in the company – with the idea that if people know who you are, it means they know because there are problems.   I understand that – and ideally, you’re just part of the woodwork, keeping problems from happening before they happen.  Which brings me to the question: How do you promote yourselves within the company?  The cuts companies are making these days are often – well, some can be said to be ‘penny wise and pound foolish’ – so what are you out there doing to make sure the folks above you who control the purse strings know what you’re doing? I’m sure we’ve all got ways that work in different environments.  Last year I did a “SQL State of the Union” presentation to the executive staff – which was high enough level to not glaze their eyes over, yet detailed enough (performance improvements and the like) to let them know they’d done a good thing when they hired me.

I think many DBA’s may already have to do a “State of the Union”, if not I think it is a great idea. What an opportunity to make your voice heard in an environment where many times if things are not broke you are not going to get any money to fix it. I work for years at a company that was more than happy to let you budget like you would get the resources that you needed. We would spend a month planning and researching what equipment that we would upgrade or replace. Sit around and look form training classes so that we could keep everyone’s skill set up to par. Work that was all done in vain, eventually every year they would come back and let us know that we needed to be at a certain amount. I can understand that we cannot run around hog wild and just purchase whatever we want however many of the times we would get budgets that would barely allow of payroll. In some years it would not.

So a “State of the Union” on your databases and database systems would give you a great opportunity to be upfront with all the risks that may be lurking. In my “State of the Union” I would address the following items:

  • Disaster Recovery – What plans are in place to recover the servers if something were to go wrong? How long would it take to bring a server back up to replace the existing? What challenges are between a disaster and a quick recovery? What is the impact if these systems got down and you cannot bring them back up for an extended period of time?
  • Security – Are you being asked to place the databases or the data at risk by allowing excessive permissions? Is there a chance that your company could be at risk for losing someone’s personal data? Is there the opportunity for improvement?
  • Morale of the team – I think this is a point that gets looked over often many companies start to cut any sort of morale boosters when the economy gets tight. When you have a staff that is working on keeping the company’s data online many times working around the clock is it a good idea to have them with a poor morale?
  • Goals Reached – Many times the DBA team is overlooked if you are doing your job right. I don’t think that is a bad thing, but you do need to make sure that the executive staff knows there is work being done. Let them know where and how you saved them money and or downtime. What has been done in the last year to increase the stability and performance of the systems?

What would you put in your “State of the Union”?

Backup Times

Posted: August 24, 2009 in Uncategorized

 

Earlier this summer I did a workshop on what I consider to be the most important thing that you can do with your database. That is back it up and restores. In my opinion this is job number one as a database administrator. Recently a friend of mine (Tom Roush) ran across a nifty little script that he uses to determine how long it will take for a backup to complete after it has started. Since it is a bit late to include in the workshop I figured that I would post the script here. Just to be clear, I am not the author of the script nor is Tom, but we found it really useful. If you are the author drop me a note so I can pass credit along.

 

 

SELECT

    percent_complete AS
‘PctComplete’,

    start_time AS
‘StartTime’,

    command AS
‘Command’,

    b.name AS
‘DatabaseName’,

    DATEADD(ms,estimated_completion_time,GETDATE())
AS
‘EstimatedEndTime’,

    (estimated_completion_time/1000/60) AS
‘EstimatedMinutesToEnd’

FROM
sys.dm_exec_requests a

INNER
JOIN
sys.databases b

ON a.database_id = b.database_id

WHERE command like ‘%backup database%’

    AND estimated_completion_time > 0

 

Colorado Springs SQL Server User Group:
Memory Management in SQL Server Analysis Services
 
Presenter(s):

Steve Wright – Steve is the Director of Product Support at SQL Sentry, which is located in the suburbs of Charlotte, North Carolina. He is a critical member of the development team behind the award winning SQL Sentry Event Manager and Performance Advisor. Steve holds MCSD, MCDBA, OCDBA 8i, and IT Project+ certifications. Prior to joining SQL Sentry in 2005, Steve developed and presented web based training courses for Wachovia Bank. Previous experience includes education positions at Techskills and Charlotte Mecklenburg Schools.

 
 

Description: 

In this presentation Steve Wright will discuss methods used to manage memory in SQL Server Analysis Services focusing primarily memory limits and preallocation.  He will also present some examples from the field of performance issues due to mis-configuration, how they were identified, and ultimately resolved.

  
 

Location:

Consortio Services

2914 Austin Bluffs Pkwy,

Colorado Springs, Colorado 80918

NOTE: This is a new location!
 
Typical Meeting Agenda:
5:30 to 6:00 pm – Arrival/Food/Networking
6:00 to 6:20 pm – User Group Business and Board Announcements
6:20 to 8:00 pm – Speaker Presentation
 
Pass it on:  
If you feel that a friend or colleague would benefit from our user group, please feel free to forward on this notice.  If you received this notice from a friend or colleague, please consider becoming a member of our user group.  There are no dues to membership but you will have the added benefits of receiving event reminders, access to the ‘Select *’ newsletter archives as well as attendance to members only meetings.  Becoming a member is quick and painless, simply go to the membership section of the user group web site which is located at: http://www.springssql.org/member_register.asp and complete the form.  That’s it!
 
Have a question?  What about a suggestion?  Topic request?
We would love to get your feedback with regards to what you are most interested in or how we can improve.  Please email your feedback to SpringsSQLMembership@gmail.com or any other board member. For additional information on this or future meetings please visit the Colorado Springs SQL Server User Group web site at www.SpringsSQL.org

Approach to Learning/Teaching

Posted: August 16, 2009 in Uncategorized

Over the last couple of weeks I have been spending a lot of my time getting ready for the fall season of Conferences, Workshops and Classes. As I was working on second edition of the SQL Server 911 Workshop I got stuck. The second in this series was designed to be all about Performance Tuning how to do it, why to do it and all that good stuff. I think the point that I kept getting stuck on is when I was taking a class I would have all this information passed to me but I would not know how to put it all together. I figured that any good performance tuning workshop is going to have to include the following subjects:

  • SQL Server Profiler
  • Performance Monitor
  • Discussions on Indexes
  • Discussion on Query tuning

I needed a way to make it a little easier to sit with an attendee. The more I thought about it I realized that it may just be as much me as what I thought the attendee would see. The process was just not clear to me. I however did find a solution. I am going to take examples of poorly written code/queries and poor designs from the last 10 years and then I am going to re-enact the whole process. Ok without the acting but the examples will be there.

SQL Quiz 5

Posted: August 10, 2009 in Uncategorized

 

Most of my SQL Quiz questions I like to keep light. Today I thought I would change the pace a touch. I really want to ask and see what answers we get on a couple of questions.

 

  1. Do you feel that you have a reliable SAN Solution? If so what is the secret?
    1. This question comes from a tune up session I was doing last year. I had about 160 attendees that I had asked to stand and once they stood I said that if you are confidant in your san and it performance the way you need it to, remain standing. I think 98% of the people that were standing sat down. I have been from client to client that has SAN issues. I know that we have to find the area that we can meet where DBA’s and SAN Administrators can find that happy place in the middle.
  2. I was recently asked to describe SQL Server Database Mirroring. The client that I was answering the question for was trying to use database mirroring for a reporting server. When I explained to him the issues around this he asked me to describe in laymen’s term what database mirroring was.

     

    My Answers

    1. If you are a DBA I think you need to start learning SAN technology. Not for a job change of conversion, but I think DBA’s that can help with the configuration of the disks are going to be helpful, even with that being said I think DBA’s and SAN Administrators are going to have to reach out and really work close together.
    2. I described database mirroring as a potentially geographically disbursed failover solution. When I sit down and use it and look at the features that I have had a chance to use I see a clone of clustering on the database level that is not bound by physical location.

 

So as always I will tag two people and hope that those 2 post the answers and then tag 2 other people.

I am going to tag

  • Brent Ozar – He is a great SAN/DBA and I think he will have some great answers.
  • Jason Strate – Good SQL Guy and would love to see what he thinks.

 

So you need a new tool, or you may be evaluating if your tool is the right tool for you. But does this mean that you have to buy the most expensive tool on the market? Over the last few weeks I have been asked at least three times about the capabilities of SQL Server and if it can handle a job or a situation. These questions have looked like:

  • Can SQL Server handle a 30TB system?
  • Can SQL Server handle all the processing that is needed for our data warehouse?
  • Whether a single SQL server 2005 or 2008 on Window 2005 can handle 5,000-10,000 concurrent connections?

In each one of the questions the person who has asked the question was already on SQL Server. One of the observations I made was in each case it appears that they have identified the database as a bottle neck. In each case there were circumstances surrounding the question that added additional difficulty. For example, the 30 TB question, can SQL Server handle that much information. The client was importing data via an ETL tool, the primary complaint was that the end to end process was not importing records quick enough. They were seeing an insert rate that was about 10% of comparable machines according to TPC benchmarks. With the hardware testing out with a much higher insert rate then what they were achieving there was obviously some items that needed to be researched. In this particular case they were measuring the rows per second by taking the total number of rows inserted into the database and dividing it by the total time. The issue with this was that when you do that there are too many steps in the process to identify SQL Server as being the issue. It would be comparable to me saying it takes me 7 hours to fly from Denver to Tucson. The flight itself is only an hour and a half, but I have to get up early to drive a couple hours to Denver, then I have to be at the airport early and then I have to wait for my bag when I get to Tucson. All the surrounding times are being figured into the total travel; however the flight is still only an hour and a half.

With the question about concurrent connections the requestor was currently running SQL Server 2000. They were looking to upgrade to SQL Server 2005 with the current workload. The question is a fair one; however there are a number of issues that could impact the number of concurrent connections. The answer is not always going to be tossing more hardware at the problem.

A few years ago I was in a shop where we were supporting close to 60 SQL Servers. I was with that company for about 5 years and it felt like at least once a year I had to fight off the question of can SQL Server do what we need it to do. This was a shop where the load could have been easily handled by SQL Server but the shop just did not see the advantage to tuning the SQL Statements or get expert SQL developers. The hard part of these cases is that if you are fighting these issues and it is an issue that is related to bad code, or a bad design even indexes that are just not there where they should be. The answer is not change RDBMS the answer is fix the problem. I think to many times people look at a database that is performing slowly and they start to think they need more hardware, or they need to change RDBM’s. When I work with performance problem systems I look at the hardware, I look to see if there is a lot of Disk I/O if there is it could be a missing index, this does not mean that added money needs to be spent to upgrade the hardware; it just means that we can use those as key indicators.

Sorry about the ramblings today. I think I am going to try to get out a new SQL Quiz tonight.

DBA School

Posted: August 2, 2009 in Uncategorized

Well many have asked so I thought I would add it here…

 

What is DBA School? This is a Class where Stephen Wynkoop and I will cover the following topics in a three day class located in Tucson AZ. The idea here is to get people in a group small enough were everyone can learn and everyone comes out of this with new contacts. The topics that we will be covering will be:


Performance – we’ll focus on management, tuning and optimization for your servers. Learn about counters to monitor, common pitfalls and native tools you can use to manage and monitor your performance and see what’s happening on your server.


Management – Ongoing management of your systems, what to be monitoring, what types of things you can do to troubleshoot and best practices for your systems.


Auditing – What are your native options for monitoring and auditing your SQL Server? What options best fit in your own implementations? What is the impact or overhead of the various approaches? What types of things do you need to be thinking about as you begin the work of satisfying compliance requirements?


Security – Planning and implementing an access control model, understanding the threats to your system, what you can do about them, how they work, tips for managing your footprint and installation vulnerabilities.


Disaster Recovery – What are the core fundamentals that you simply must provide for your systems? What types of tools (online and offline) can you use to manage your recovery process? What happens if a restore fails? What options do you have for planning and deploying your systems and processes? What checklists do you need to have at your fingertips? How do you respond to a critical system issue?


Very Large Databases – You’ll find out about considerations for performance, management and things you’ll want to know for your larger systems. We’ll explain and show how you can use tools to help distribute load and we’ll go into how larger systems will impact your planning for recovery and management.

We have a great rate on a hotel here and we even have some local sites that you cannot miss if you are in town an extra day.

Please check out the Class here.