Hello folks. Even more free training is coming your way in February from the PASS Virtual Chapters. At least 3 of our chapters have scheduled training sessions I want to let you know about. More press on these should be available in the next week. I can’t speak for the other VCs but I know those of us at the AppDev chapter are very open to doing sessions at time slots other than noon eastern (if fact you’ll see that later this month) but we need feed back as to what time you would like to have us bring you a session. (and by feedback I don’t mean flame-mail!) PASS AppDev Presents: SQL Server Indexing Date: Start Time: End Time: Time Zone: Add to your calendar 2/9/2010 12:00 PM 1:00 PM GMT-5 Click Here Event Description: While indexing traditionally has been the concern of the DBA, it’s important for developers to understand both the usefulness and the impact of indexes in your relational database. With the right indexes your application will perform like a Formula One racer, without them performance will better resemble a Model T. This presentation will explain how indexes work, what options are available to you in SQL Server 2008, and how to tune your application and your database for the best performance. Allen White: Allen is a consultant and mentor for Upsearch Technology Services in Northeast Ohio. He’s spent over 35 years in IT and has been using SQL Server since 1992 and is certified MCITP in SQL Server and MCT. Allen has been awarded Microsoft’s MVP Award for the last three years. He’s active in the Ohio North SQL Server User’s Group and contributes in the MSDN Forums, answering questions about SMO and PowerShell, and maintains a blog at http://sqlblog.com/blogs/allen_white/default.aspx. How do I view the presentation? Attendee URL: Live Meeting link _____________________________________________________________________ PASS Virtualization Presents: SQL Meets Virtual CPUs, Memory, and Storage Date: Start Time: End Time: Time Zone: Add to your calendar 2/9/2010 12:00 PM 1:00 PM GMT-5 Click Here Event Description: SQL Server can run great in virtual environments like Microsoft Hyper-V and VMware vSphere, but you can dramatically increase the odds of success if you know which knobs to tweak. Just like SQL Server, virtualization software doesn’t always work best with the default settings, and knowing which settings change SQL Server performance is key. In this session, Brent will explain: Why virtual CPUs, memory, and storage are different Hidden gotchas that often cause virtual performance How to stress test your virtual server to find limits Brent Ozar: Brent Ozar is a SQL Server Expert for Quest Software, a systems management company. Up until a year ago, Brent managed the VMware, storage area networks (SANs) and SQL Servers for a $7b company. He’s used virtualization on the desktop and in the datacenter, and he can explain the options with real-world experience. He coauthored the book Professional SQL Server 2008 Internals and Troubleshooting. The Where and When To get the attendee links, go to Virtualization.SQLPass.org, and click on Register at the top right of the page to sign up. We’ll email all members before the webcast. Even though this is a SQLPass.org web site, they don’t share the same username/passwords. You’ll need to register again with a new login to attend the meeting. Thanks for your understanding! ______________________________________________________________ PASS DBA VC Presents: SQL Server Locking & Blocking Made Simple Date: Start Time: End Time: Time Zone: Add to your calendar 2/10/2010 12:00 PM 1:00 PM GMT-7 Event Description: A good working knowledge of how SQL Server makes use of locking and transaction isolation levels can go a long way toward improving an application’s performance. In this session, we will explore SQL Server’s locking methodology and discover techniques for enhancing query response times. Joe Webb: Joe Webb, a Microsoft SQL Server MVP, serves as Chief Operating Manager for WebbTech Solutions, a Nashville-based consulting company. He has over 14 years of industry experience and has consulted extensively with companies in the areas of software development, database design, and technical training. Live Meeting Information: Attendee URL: Live Meeting Link
We’ve got some great free training coming up this week from the AppDev and DBA Virtual Chapters of PASS. I personally am really looking forward to “SQL Server Consolidation: How to decide on the right approach (between DB, VMs, Instance)?” It sounds like a really good session to me Please note that the “Defensive Programming’’ session has been timed for our international members. We’ll have more news on upcoming session probably early next week so please check back soon. PASS AppDev VC Presents: Best Practices for Exception Handling and Defensive Programming in Microsoft SQL Server Date: Start Time: End Time: Time Zone: Add to your calendar 1/26/2010 8:00 AM 9:00 AM (GMT-05:00) Eastern Time (US & Canada) Export to Calendar Event Description: This is a deep dive developer session that explores error handling in SQL Server. The session focuses on core error handling areas such as understanding the anatomy of errors, detecting errors in TSQL and best practices for error handling and defensive programming in Microsoft SQL Server. Jacob Sebastian is a SQL Server MVP and is working with SQL Server for over 11 years. He is a Moderator of MSDN / TechNet Forums, Regional Mentor for PASS Asia and a regular columnist at SQL Server Central. He is the president of Ahmedabad SQL Server User Group and a regular speaker in SQL Server events in India and USA. Jacob is the author of “The Art of XSD – SQL Server XML Schema Collections”, a contributing author in “SQL Server 2008 Bible” and is currently writing a book in FILESTREAM. He blogs at BeyondRelational [http://beyondrelational.com/blogs/jacob/] How Do I View the Presentation? Follow the link and join the LiveMeeting. Audio: Audio will be provided via computer speakers. PASS DBA VC Presents: SQL Server Consolidation : how to decide on the right approach (between DB, VMs, Instance)? Date: Start Time: End Time: Time Zone: 1/27/2010 12:00 PM 1:00 PM (GMT-05:00) Eastern Time (US & Canada) Event Description: This session will focus on helping to choose between using a virtualization, instance, or database consolidation option. We will highlight a few of the key areas to consider as well as some of the important differentiators to keep in mind. We will provide a decision tree to help guide administrators through the process of selecting a consolidation option. Objectives: 1. Provide administrators with a decision tree for choosing a consolidation option 2. Highlight key decision criteria and differentiators 3. Discuss hardware considerations 4. Highlighting customer case studies and deployment scenarios Sung Hsueh of Microsoft will be speaking about how to choose between using a virtualization, instance, or database consolidation option. We will highlight a few of the key areas to consider as well as some of the important differentiators to keep in mind. We will provide a decision tree to help guide administrators through the process of selecting a consolidation option. Location: Online, via Live Meeting [https://www.livemeeting.com/cc/usergroups/join?id=4HG9C2&role=attend] And as always, please feel free to leave a comment here if you would like a topic presented or if you would like to do a presentation.
Well my first public presentation is in the bag. It was easier than I thought, got good reviews but not without glitches: Average score was a 4.1 ( out of 5 ) Some items were missed during prep. I forgot to go back to the slide deck and the end. No blog post before the presentation told folks what code I was going to show during the presentation (will post that later this week). I made the egregious error of typing during a demo. I will never try not to do that again. Even after I did a copy and paste of working, un-edited code, it still didn’t work. Anyways out of the packed room of people at my session, when I asked people to raise their hand if they’ve used PowerShell before today only about 20% or so raised their hands. What did I do right? I helped people really grasp some of the core differences between SQL Server and Power Shell–like how when you run part of a script and load some data into PowerShell variables that data persists for when you need it later. This is completely different from how T-SQL works and was literally a jaw-dropping point for some of the people in the room. The other thing I did right was to partner up with a speaker that knows more about PowerShell than I do. A safety net of knowledge is a wonderful thing! Finally. The whiz-bangs I had planned for the end of my demo worked great. (Look for those whiz bangs to start showing up on here in a few days.) This was a great learning experience for me and hopefully it provided some helpful tips to the good folks who came to my session. I started out the session with a little video from Daft Punk. The video goes through some hand motions with words written all over the hands. I got to use PowerPoint for the first time in my life. I wonder if I could have put the video in the PowerPoint? (Jennifer says yes: http://tinyurl.com/yc7ndy8.) Hopefully I can learn from all these lessons and provide an even better session next time. As promised, here is the link to my PowerPoint slide deck Can’t wait for Richmond this Saturday, catch you there!
Tomorrow I am presenting to a non-captive audience at SQL Saturday #32 in Tampa. I’ve done presentations on the wonderfulness of SQL Server before but this will be the first time where the people have 6 other great options to learn about SQL Server. I’m not used to competing for audience! My talk isn’t directly about SQL Server. Instead I’m focusing on PowerShell and it’s ability interrogate your SQL Server(s). Very cool stuff if you’re a data nerd. At any rate I hope you stop by, heckle me say hello and stick around to learn some of my favorite PowerShell tricks. Here’s a look at what I’m covering: PowerShell for Data Professionals In this session we will learn to perform several everyday data tasks including user database backups, scripting table objects and evaluating disk space usage with PowerShell. For each task we will explore the benefits of using PowerShell over the standard method. Only a basic understanding of PowerShell or DOS is needed. This session should serve as a good introduction to PowerShell for database users. The goal is to help everyone get their feet wet using PowerShell. The event has a lot of great sessions so I hope that you can make it out. Please checkout the full schedule while I get back to working on the last of my demo scripts: Start Time Track 1 Track 2 Track 3 Track 4 Track 5 Track 6 Track 7 Brian Knight Geoff Hiten Jack Corbett Bayer White Michael Mollenhour Troy Gallant Michael Antonovich 9:00 AM Introduction to SSIS Clustering for Mere Mortals Why Should I Use Stored Procedures? Getting Started with AppFabric Caching (Velocity) Accurate MDX RoadTrip Introduction to Transactional Replication XML 101 for the SQL Developer Eric Wisdahl Jeffrey Garbus Plamen Ratchev Andy Warren Aaron Nelson Vikas Hawaldar Nathan Heaivilin 10:15 AM SSIS – A Beginning Framework Choosing Indexes for Performance Refactoring SQL for Performance Social and Not So Social Networking for the DBA PowerShell for Data Professionals Data Mining and Predictive Analysis using SQL 2008 Introduction to Execution Plans Brian Knight Dmitri Korotkevitch Plamen Ratchev David Dye Chad Miller Mark Landry Telmo Sampaio 11:30 AM Iron Chef Competition: Loading a Data Warehouse Getting familiar with SQL Server Storage Engine Common SQL Programming Mistakes Implementing Auditing in SQL Server 2008 PowerShell ETL Building a Near-Realtime Operational Dashboard SQL Server Beyond Relational Scott Gleason Jorge Segarra Herve Roggero Kevin Boles Ronald Dameron David Taylor Michael Stark 1:30 PM PGP encryption using SSIS SQL University 101:Starting the SQL Journey SQL Azure Care and Feeding of TempDB Database Hardening via PowerShell Using SSRS with Trending Reports SQLServer 2008 Spatial Data Christian Loris Jonathan Kehayias Scott Klein James McAuliffe Patrick LeBlanc Patricia (Pat) Baxter 2:45 PM SSIS and Slowly Changing Dimensions Real Time Problem Identification with Event Notifi Developing with SQL Azure Using New Data Types and Features of SQL 2008 SQL Server Data Compression 101 5 Resolutions to Build Your Professional “Brand” Mike Davis Jason Strate Elijah Baker Jeffrey Garbus Adam Jorgensen Janis Griffin Kevin Boles 4:00 PM Migrating DTS Packages to SSIS ABCs of CTEs Database Table Partitioning, The Next Step Bad, less Bad, not Bad; rewriting bad SQL Code Zero to Cube in 60 Minutes Tuna Helper for SQL Server DBA’s SQL Server Service Broker See you Saturday!
Before I even started this year I knew that I was going to try and hold myself to putting out at least 52 blogs this year. Then I noticed that Jeremiah Peschka posted on this thing called “Project 52”. Since I’m already holding myself to that goal – even though I failed to list it on my Goals for 2010 – I thought I’d mention it here publicly. Project52 is a personal challenge geared toward getting fresh content on your website. The goal is to write at least 1 new article per week for 1 year. Because we all know what it‘s like to procrastinate on our content. A website is not just a fresh design that can be uploaded to the web and forgotten about! Hey does this count as one of the 52?
Yesterday I did a quick Lunch-n-Learn here at my company on a few SQL Server 2005 features and this post is just a follow-up to remind everyone what we saw and where you can find those features. I will try to still make this useful for those of you who didn’t attend. 0: To make any of these code examples work please download and install the Sample Databases for SQL Server. (Don’t worry, they’re pretty small.) The first thing that you folks saw was the intelli-sense where as I was typing the name of a table and SQL Server Management Studio (SSMS) was finishing the table name for me. That was the only SQL Server 2008 exclusive feature that you saw in the entire session. You can only see this if you have the SSMS 2008 client pointed to a SQL 2008 db. I showed you guys how to find out the name of every table and column of the db that you are in using the system views: SELECT * FROM INFORMATION_SCHEMA.TABLES SELECT * FROM INFORMATION_SCHEMA.COLUMNS Ticks! How did this apostrophe get into our db? And how do we get it out? SELECT [Comments] ,[ProductReviewID] ,[ProductID] ,[ReviewerName] ,[ReviewDate] ,[EmailAddress] ,[Rating] ,[ModifiedDate] FROM [AdventureWorks].[Production].[ProductReview] Well you just use 2 ticks to insert a single tick. Said again, you use “’’” to insert “’” into SQL. Here’s an example from the AdventureWorks database: SET IDENTITY_INSERT [Production].[ProductReview] ON INSERT [Production].[ProductReview] ([ProductReviewID], [ProductID], [ReviewerName], [ReviewDate], [EmailAddress], [Rating], [Comments], [ModifiedDate]) VALUES (6, 709, ‘John Smith’, CAST(0×0000941800000000 AS DateTime), “>‘firstname.lastname@example.org’, 5 , ‘I can”t believe I”m singing the praises of a pair of socks, but I just came back from a grueling 3-day ride and these socks really helped make the trip a blast. They”re lightweight yet really cushioned my feet all day. The reinforced toe is nearly bullet-proof and I didn”t experience any problems with rubbing or blisters like I have with other brands. I know it sounds silly, but it”s always the little stuff (like comfortable feet) that makes or breaks a long trip. I won”t go on another trip without them!’, CAST(0×0000941800000000 AS DateTime)) SET IDENTITY_INSERT [Production].[ProductReview] OFF And we can get it out like this: UPDATE [Production].[ProductReview] SET Comments = REPLACE(Comments, ””, ”) WHERE ProductReviewID = 6 To make your keyboard as useful as mine what you do is open up SSMS click on Tool > Options > Environment > Keyboard >and add the commands that you would like the corresponding shortcut key to execute. What Exactly is XACT_ABORT? When XACT_ABORT is ON SQL Server will stop and roll back the transaction as soon as it hits an error; it won’t continue processing all the way to the end. “When SET XACT_ABORT is OFF, in some cases only the Transact-SQL statement that raised the error is rolled back and the transaction continues processing.” Use this script to see it work for yourself. The next feature that I showed you was how you can create an Identity Column for the data you are selecting without having to insert the data into a table by using the ROW_NUMBER() function. I mentioned several other features along with that one and I will demo those features in our next Lunch-n-Learn. In the meantime here’s a script that will show you it do 4 different row numbers on 4 different fields all at the same time. /* SQL Purists: please don’t freak out at this script, I’m just showing functionality*/ SELECT TOP 500 * ROW_NUMBER() OVER (ORDER BY AvgCPUTime DESC) AS ‘AVG Time Rank’ , ROW_NUMBER() OVER (ORDER BY total_cpu_time DESC) AS ‘Total CPU Rank’ , ROW_NUMBER() OVER (ORDER BY total_duration_time DESC) AS ‘Total Duration Rank’ , ROW_NUMBER() OVER (ORDER BY total_execution_count DESC) AS ‘Total Executions Rank’ , a.TEXT AS ‘StatementText’ , a.total_cpu_time , a.total_execution_count , a.total_duration_time , a.AvgCPUTime , a.number_of_statements , a.plan_handle , SUBSTRING(a.TEXT, 1, 100) AS ‘hundy’, a.name FROM ( SELECT sql_text.TEXT, SUM(qs.total_worker_time) AS total_cpu_time,� SUM(qs.total_elapsed_time) AS total_duration_time,� SUM(qs.execution_count) AS total_execution_count, SUM(qs.total_worker_time) / SUM(qs.execution_count) AS AvgCPUTime, COUNT(*) AS number_of_statements,� qs.plan_handle , db.name FROM� sys.dm_exec_query_stats qs CROSS apply sys.dm_exec_sql_text(sql_handle) AS sql_text LEFT OUTER JOIN sys.databases db ON sql_text.dbid = db.database_id –WHERE dbid = 10 GROUP BY sql_text.TEXT, qs.plan_handle , db.name )a ORDER BY AvgCPUTime DESC Comparing which way is better. To compare how long and how much data was needed to satisfy two different queries you can just click on the Include Client Statistics button before you run the first query and it will start capturing the statistics for you: And finally the built-in reports. To run any of them just Right-Click any database in SSMS Object Explorer and navigate to the report that you want: That pretty much wraps it up for what we covered yesterday. I will put some examples together for the Common Table Expressions (CTE), RANK, DENSE_RANK, and NTILE fuctions that I mentioned yesterday so that we can go over them at the next one. Next Tuesday work good for you? If you would like anything else covered you can just email me or comment here.
Today’s post about changing your Max Server Memory setting is to answer the call from Adam Machanic about Puzzling Situations . A couple weeks before I headed out to the 2009 PASS Summit I encountered a puzzler of my own. One of our servers issued an alert that it had an extremely low Page Life Expectancy (PLE); like 16. All of the databases on this server had recently migrated from an older 32 bit server with 4 GB of RAM to thier current 64 bit server with 8 GB of RAM. As luck would have it, this was the source of my puzzling situation. When we migrated the dbs we inadvertantly transfered the memory configuration used in their old 32 bit server home. Who made this classic rookie error?? Yours truly. I was doing two server migrations at once and bobbled the checklists. I rectified the situation by increasing the RAM settings from 3 GB to 6 GB: sp_configure ‘max server memory (MB)’, ’6144′ RECONFIGURE Want to guess what happened when I ran the RECONFIGURE command? I watched as the perfmon counters immediately went down! Indicating that memory usage had dropped rather than increased. As it turns out in SQL Server 2005 (and in SQL Server 2008 as far as I know) when you run RECONFIGURE for anything you dump the procedure cache. So for example if you were to change the setting for ‘”Web Assistant Procedures” you would dump the procedure cache. If you were to change the setting for “fill factor (%)” you would dump the procedure cache. So beware before you change a configuration setting in your Production environment and run RECONFIGURE. So there’s my Puzzling Situations for T-SQL Tuesday #002
It’s that time of year so here’s a quick blog post so that I can track how well I achieve my goals for 2010 My goals for 2010 are pretty simple. Spend more time with my daughter Finish reading my PowerShell In Action book Help run and market a very successful year of the App Dev Virtual Chapter for PASS Not Die (It’s George Carlin’s secret to life after all) Get certified in Hyper-V: Windows Server 2008 R2, Server Virtualization Do at least 4 different speaking engagements on at least 5 different topics (meaning: User Group, Lunch-N-Learn, SQL Saturday, SQL Lunch, Code Camp??) Use PowerPivot at least once before May Read a second PowerShell book from cover-to-cover Get at least one report to do something/anything with Map Point 2009 Show the value of community so that I can get my company to pay to send me to the PASS Summit once again. Write a scathing review on every book that I read Just checking to see if you’re really paying attention Get every db in my current company upgraded to SQL Server 2008 SP2 Get my backyard back under control Convince PASS to move the 2011 Summit to somewhere within the Eastern or Central Time Zones. And of course: drop 20 pounds. And why am I bothering to post this you might ask? Well I hear the way to make God laugh is to tell him your plan.