Tag Archives: 24HOP

Stress Testing Stored Procedures with Free Tools

I currently have a presentation named Performance Tuning for Pirates. This presentation goes over using several free tools to help you with your performance tuning process. Today, I am happy to share my first video going over these tools.

Using SQLQueryStress to stress test Stored Procedures

SQLQueryStress by Adam Machanic (blog | twitter)  is one of my favorite tools for doing a quick stress test against a user object like a stored procedure. This tool can actually be used to do stress testing on T-SQL queries but I usually use it for compiled objects like stored procedures.  I love this tool because it allows you to save and load configurations to quickly redo tests. It also allows you to use a dataset for input parameters and allows you to add additional stress with configuring iterations and threads.

Note: It also comes in handy when you need to mock up some stress for demos.

I also written a tip for www.mssqltips.com that goes over a step by step guide on how to use SQLQueryStress.

#tsql2day – Contained Databases are sexy and they know it!

This month Nigel Sammy (blog | twitter) is hosting this months T-SQL Tuesday and the chosen topic is  What do you think is a useful feature of SQL Server 2012? Therefore, I am going to write about a topic that isn’t getting much pub in the streets like AlwaysOn Available Groups or Columnstore. But that’s okay because Contained Databases are sexy and they know it!

Contained Databases

A contained database is a database that is isolated from other databases and from the instance that hosts SQL Server. Why could this be helpful? Well lets think about logins. What happens when you migrate a database from one instance to another? You have to copy over the logins and unorphan the logins if they are orphaned.  Wouldn’t it be nice to keep the logins contained in a database so they weren’t depended on logins created on the instance? I think so, especially for some third party applications that think they need sysadmin access.  Now why would I be worried about databases moving between instances? This is where Always Avaiable groups come in. I know what your thinking, he just lied and is talking about Always Available groups. Yep, your right but here is my point.  If the users are contained users for a database belonging to an Always Available group, during a failover, people would be able to connect to the secondary without creating logins on the instance hosting the secondary. This was a cool lesson learned quickly while building my virtual sandbox to test Always Available Groups.

Before you consider using contained databases you have see if your user databases are able to be a contained databases. If you are using replication, change data capture, or change tracking then contained databases may not be for you. As always, review Books Online for more details and make sure you test this out in development before using it in production.

Bonus – Get your Learn On!

Are you interested in learning more about SQL 2012? Do you wonder where you can find free resources? If so, the links I am using are included in this paragraph to help you brush up on the good stuff. You can download a free book, get your hands dirty with several virtual labs, view several videos and demos, Watch recorded sessions from #24HOP and attend the Microsoft Virtual Academy SQL 2012 training sessions.

24 Hours of PASS – Get your free downloads!

I am speaking at 24 Hours of PASS

I am speaking at 24 Hours of PASS

Thank you to everyone who attended my #24HOP session on Performance Tuning for Pirates! You can now download my slide deck, t-sql queries, and view my reference links.  If you have any questions about the tools presented feel free to contact me and I will  try to help you out or point you in the right direction.


6 ways to get free SQL Server 2012 Training

I always get excited when a new version of SQL Server gets released and rumor has it that  SQL Server 2012 will be released on March, 7th 2012. Why do I get excited? Well its quite easy, I know that each new release includes new features. These new features make my job easier. How do I know that these new features make my job easier? I know because I test them out and attend training sessions to see how other people are using them.

This year Microsoft is teaming up with the Professional Association of SQL Server Professionals (PASS) to provide several opportunities to give free SQL Server 2012 training.   Here are six ways you can learn about SQL Server 2012 and it won’t cost you a cent.

Online Labs

I love to get my hands dirty and throw things against the wall and see what sticks. Microsoft provides several virtual labs that allow you to do this without worrying about your servers.  If you want to checkout upgrading to SQL 2012, AlwaysOn Availability Groups or Installing SQL 2012 on Windows Core they have labs up and waiting for you.

Special Ops Tour

If you like to physically experience a launch event and rub shoulders with others who are using SQL Server you have to take part in the Special Ops Tour.  The Special Ops Tour is a 12 city event where Microsoft experts and local DBA’s will show you the must use features coming in this new release.  You can still sign up now. Hopefully, there is an event near you.

SQL Server 2012 Virtual Launch

Microsoft is also providing an all day virtual launch on March 7th that will include several topics on Mission Critical Confidence, Cloud on your Terms and Breakthrough Insights.  You can review the agenda and there still is room to sign up.

24 Hours of PASS

Once again, PASS is gearing up to deliver another 24 hours of PASS on March 21, 2012.  For those who don’t know 24 hours of PASS is  an in-depth look at the hottest SQL Server topics over 24 back-to-back free technical webcasts. You can gain access to the best SQL Server training and several of these session will be focused on SQL Server 2012. For more information visit the 24 hours of PASS website. If you want to speak get your abstract submitted now. The deadline is today!

SQL Saturday

SQL Saturday is an all day multi-track training event for SQL Server professionals and those wanting to learn about SQL Server. These events are spread all across the USA and the globe.

User Group Meetings

User Groups are monthly or bi-monthly meetings held to allow PASS members to connect, share and learn on a local level. There are several User Group’s spread out across the country.  Do you wonder if there is a user group in your area? You can find all the user groups here.

Recap: #24hop (24hrs of PASS) – Day One

I am very happy that the committee behind #24HOP made two decisions.  One they decided to split the 24 hours into two days.  This is huge for people in the USA as we don’t have to pull all nighters.  Second, I am very glad day one fell on a Wednesday.  Why would I be exited it falls on a Wednesday?  I am excited because it is no pants Wednesday.  No pants Wednesday  means I don’t work on Wednesday’s so its very easy to attend sessions.

Day Two

If you didn’t catch it in the first paragraph there is a day two.  That’s right peeps you can still signup and attend some great sessions.  If you need some help picking a session or two I wish I could attend the ones listed below.

The following is a short review of the sessions I attended on September 15th 2010.

Gather SQL Server Performance Data with PowerShell

Allen White (Blog | @SQLRunr) showed a very slick way to automate the process of collecting WMI counters and save them in a database.  This alone was very slick but to add the icing on the cake he also showed the crowd how to build reports that work inside of SSMS.

My eyes were opened up wide when I saw how easy it was to do WMI and SQL calls with PowerShell.  I will defiantly check out http://powershell.com in the near future to get my learn on.

It looks like Allen has a great PreCon session for the SQL PASS Member Summit 2010 lined up that will get you well on your way with automating your DBA tasks.

Hardware 201: Selecting and Sizing Database Hardware for OLTP Performance

Glen Berry (Blog | @GlenAllenBerry) ran through tons of statistics behind selecting CPU’s, Memory and Disk’s for your new database servers.  I have to be honest quite a bit of this was over my head but below are a few items that stuck.

  • Optimize your hardware purchases to take advantage of your SQL and Windows Server editions
  • Don’t go cheap on CPU’s. You rarely upgrade the CPU unlike RAM or disks.
  • Xeon X5680 and Xeon X7560 were recommended CPU’s
  • SSD (solid state drives) are good for random writes (user data files) not sequential writes (log files)
  • 10K drives = 100 IOPS
  • 15K drives = 150 IOPS
  • Make sure High Performance is enable in power settings on your servers

Identifying Costly Queries

Grant Fritchey (Blog | @GFritchey) showed us several different tools you can leverage to identify costly queries.  He showed us how to setup SQL Profiler using stored procedures to lessen the load on your production boxes.  Grant also showed me a new tool I haven’t used before. This was the SQL RML Utility tool that can be helpful show how long a query really took.  Grant also showed us server DMV’s that can be used to get real-time understanding costly queries.

For some samples and resources used in the demo check out his resources blog page.

How to Rock Your Presentations

Douglas McDowell (Web | @douglasmcdowell) delivered the most important session for me.  This year I started to focus more on giving back to the community through technical presentations.  I am always looking for some tips that will improve my presentations.  The following were a few tips I plan to implement on my current presentation schedule.

  • Treat presentations like a development project
  • Storyboard each topic
  • Build an outline
  • Make sure to add RM, WIIFM and KWUC to all presentations.

You can find more in his PowerPoint presentation at http://downloads.solidq.com/DMcDowell/RockPASS_DMcDowell.zip


This was another great day of #24HOP.  The best part is it continues today.  The sad part is I will miss out on the sessions.  If you catch them and have good notes.  Please add them as a comment or blog them so the unlucky ones can check out the info.