#sqlrally notes from Friday

15 May

New Reporting Features of SQL Server 2011 by Michael Davis

Posted online was a session by Randy Dyess who I know because he was onsite mentoring us on some data warehousing. I’m trying to get a server dashboard report going and a custom IIS log report but that is the extent of my need for BI. I do have to administer RS. When I walked by the session title was actually like Administering RS 2008 R2 which is a weakness of mine.

There was a great question (if i do say so myself) about integrated security vs. stored credentials. Also, someone else commented later on the pains of getting integrated security working, which I have felt myself. Mostly SPNs and delegation problems but thats another story.

Michael gave very demo oriented explanations that went very well. It caused my notes to be a bit scattered but that is fine with me.

A shared dataset is something that multiple reports can consume. Creating a pathway for DRY reporting.

We didn’t get into the report building but BIDS or Visual Studio to experiment with report deployment.

-setup data sources
-deploy datasets
-could also deploy whole bids project
-.rdl files are created

Data driven subscriptions
-have to share credentials
-many report delivery options
-path has to be a share
-creates sql agent job with GUID name (don’t change)

report logs are in the database but not very readable -> see codeplex SQL Scrubs

CONVERT(char(10), GETDATE(), 112) to append date to a report filename… just add it to your query

good idea to setup shared schedules ->monthly weekly daily etc…

Schedule caching
-can save a rpt snapshot for performance
-test different caching rules

Showed some report security and that you can break inheretance, and then revert back to parent if you need to

For users you will want to enable myreports, use SSMS/with RS connection


SQL Server Troubleshooting via DMVs by Steve Schneider

Definite highlight of the week. I’ve seen good ones and duct taped some of the DMVs together myself but Steve has definitely put a “nujakcities certified sweet” stored procedure together.

He gets a very thorough snapshot of a server, or multiple servers if you have CMS setup.

Use process explorer to identify system processes and their resources

the SP is granular to the table, query text, even pointing out APIs and % of table that is in memory

The rest of the notes for this will come later once I get my head around the code


Evaluate your daily checklist against 100+ instances of SQL Server while you get a cup of coffee by John Sterrett

John covered mostly Policy Based management features. One of his key points was a hint of professional development to create a morning checklist. Then automate it.

Use CMS and register servers. Store the policies on this server and evaluate them on all other servers

Use powershell’s ISE to script against CMS

the EPM framework is what SSMS and Powershell use to interact with the servers, this is downloadable

Use PBM for mirroring because SCOM not so good


Deadlocking for Mere Mortals by Jonathan Kehayias

Yet another weakness of mine. The concept I avoided because my thought pattern was… get the queries running faster and they won’t run into each other, for now.

Jonathan started with a joke like fact that nolock will fix all dead locks. What made it hilarious was how terrible an idea this is to apply nolock.

– if you have similar transactions in your environment, make sure they work with objects in the same order
– Majority of deadlocks are a bookmark lookup deadlock, caused by clustered key and nci contention
— spid grabs lock on clustered index for update other spid grabs lock on nci for select

TFlag 1222 will start logging deadlocks
-sql server log can have all the info needed to resolved

Profiler “Deadlock graph event” will show on SQL TV the deadlock image.
-.xdl files are deadlock files that can be opened in SSMS

Read committed snapshot can help, but buyer beware, will increase tempdb utilization

Isolation level of serializable will take range locks which will block updates into that range


Zero to OLAP Cubes in 60 Minutes by Adam Jorgensen

download the contosoDW and open up a new BIDS project
-import from a deployed project (nice feature)

Work the way down the folders on the right setting up elements of this AS project

Cleanse the data with:

-Friendly names (in properties)
-named calculations ( turn M into Male )

don’t use named queries

Cube creation time
– use existing tables
– don’t use gen tables

New dimensions
– set attribute

Deploy to the AS server consume with Excel Data Connection Wiz and bazinga…


Fundamentals of SQL Server Internals by Jeremiah Peschka

What a splendid speaker choice considering I was reading his bosses co-authored internals book on the flight down.

Jeremiah walked the crowd through the engines response to our queries.

after networking protocols comes parser
-this gives a much better error in 2008 than it used to

then the optimizer (cost based with secret sauce algorithms)
-select * is not optimized
– can view QP to see optimization level
– multiple passes but only if not trivial
– user defined functions don’t have plans

Storange engine
data access methods
-moves data from/to mem or disk

forcing lock levels will disable lock escalation

locks use significant amount of memory

once scheduler per CPU

And brain has checked out to to disney world. I noticed there was a damn cd of the TOP PASS sessions… yay more homework.

1 Comment

Posted by on May 15, 2011 in PASS, SQL Admin, SQL Dev


One response to “#sqlrally notes from Friday

  1. Thomas LeBlanc

    May 16, 2011 at 3:01 pm

    good review dude. Glad I got the chance to visit with you and Time.



Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: