Please help spread the word to anyone who may want to attend this free users group meeting and presentation.
The November meeting of the Bellingham SQL Server Users Group (PASS Chapter) will be held on Wednesday November 18th at 6:00pm.
Welcoming Ron Talmage from SolidQ with his presentation on Building a Static Data Warehouse Server.
The agenda for the meeting will be:
– Welcome and Pizza
– Chapter news and announcements
– Technical presentation (Ron Talmage)
– Socialize and networking
The chapter meeting will be at the following address. Thanks to Derrick Bovenkamp for providing the location for this meeting.
709 West Orchard Drive #4
Bellingham, WA 98225
This is the office right next to Jeckyl and Hyde Ale House on Orchard Drive.
For any questions, please contact Steve Stedman (360)610-7833
Building a Static Data Warehouse Server
Sometimes users require underlying data to be static and unchanging. In this session we’ll examine steps used to create a static server from a copy of a live data warehouse server, including implementing clustered columnstore on large partitioned tables, simplifying the management of files and filegroups, and reclaiming large amounts of database space.
Presented by Ron Talmage – Mentor and Co-founder, SolidQ
Ron is a mentor and co-founder with SolidQ. He is a SQL Server MVP and current chapter leader of the Pacific Northwest SQL Server Users Group. He has authored numerous SQL Server white papers and contributes to SQL Server technical publications.
Friday morning at 8:00am at PASS Summit 15 in Seattle, I will be presenting my Advanced Common Table Expressions and Recursive Query Technique session.
Advanced Common Table Expressions
Here is the abstract:
You might have been introduced to Common Table Expressions (CTEs) and understand the WITH syntax, but want to know more. Learn how recursive queries work with CTEs and how to display hierarchical data. Did you know that you can INSERT, UPDATE, and DELETE data from CTEs? This session covers some common use cases for CTEs, including finding holes in patterns, finding and removing duplicate data, string parsing, and more. Get an in-depth understanding of the performance behind a CTE and learn when a CTE is the right (or wrong) solution. Finally, take a look at some classic recursive algorithms and how they can be implemented with CTEs.
For more details check out the session on the PASS Summit 2015 website.
There is a long history behind this presentation, it originally started as a presentation at SQL Saturday #108 in Redmond in 2012. From there I presented it at several other SQL Saturdays including Vancouver BC in 2012. After presenting on CTEs, at about 8 different SQL Saturdays, I eventually split the session into two, the Introduction to Common Table Expressions, and the Advanced CTEs. This was around the same time that I finished my book on Common Table Expressions which released the day of SQL Saturday 212 in Redmond in 2013. I found that once it was split into the Introduction to Common Table Expressions and Advanced Common Table Expressions that the Advanced session was much more popular.
IF you are browsing your error log and come across an error message stating that “1 transactions rolled forward in database ‘msdb'”, or “X transactions rolled back in database ‘msdb'” you might be a bit alarmed. You might also notice similar error messages for master, tempdb or user databases.
How can this be a good thing? Why are transactions being rolled back or rolled forward?
Here is a discovery that I made using the Database Health Monitor historic wait monitoring, on a server with slow storage where the backups were being written.
If you are seeing excessive waits on the PREEMPTIVE_OS_GETPROCADDRESS wait type and xp_create_subdir is the command with the wait, and this is occurring at the time your backups are being run, it is a symptom that the storage location for your backups is having I/O difficulties.
I noticed this on a server with an external USB 2.0 attached hard drive that was being used for backups, and on a second server with a USB 3.0 external hard drive. When the backups run, there was a wait for the process to attempt to see if the backup directory exists, and to create it if it did not.
The Quick Scan report in Database Health Monitor detects when you are performing SQL Server backups without compression, when the compression option is available. This applies to Full backups, Transaction log backups and Differential backups.
Not using compressed backups? Why not?
Benefits of compressed backups
Faster backup time
Faster restore time
Less I/O at backup and restore time
Since you are using less disk space, you can keep more backups around.
There are a couple minor drawbacks, the compressed backups take more CPU, not much more, but a tiny bit more. Also the compressed backups don’t compress very much when your database is using Transparent Data Encryption (TDE).
If you have more memory than your database and applications on the SQL Server will ever use than this is not a problem, but when you run into memory constraints this setting is much more important.
SQL Server attempts to use as much memory as possible, and when there is no more memory available, SQL Server will use much more I/O due to data and index pages having to be read from disk more often. This works great for SQL Server, but what happens is that SQL Server will take almost all the memory leaving very little for the operating system processes or other applications that are running.
The default for this setting is 2147483647 which is probably more memory that your server has, which effectively tells SQL Server to take as much memory as it wants to.
In SQL Server there is a special connection that can be used to connect to the database, the remote Dedicated Admin Connection or (DAC). This is a special connection that allows the administrator to connect to the SQL Server even if something has occurred that is preventing connections on the normal port.
One quick way to check if the DAC connection is available is to try connecting to the server name with ADMIN: in front of it from SSMS, like this:
If the DAC connection is not enabled, you will see an error message that looks like this.
Another way to see if it is enabled is to open a normal connection to your database and run a query to check.
EXEC sp_configure 'remote admin connections';
If the running value is 0, then it is not configured. If it shows 1 then it is configured.
Using TSQL you can turn on the DAC like this:
EXEC sp_configure 'remote admin connections', 1;
Be sure to run the RECONFIGURE command to allow the change to be activated.