You can download content for ASP.NET, Office, SQL Server, Windows Azure, SharePoint Server and other Microsoft technologies in e-book formats such as PDF, E-Pub or Mobi (for use on Kindle). There’s different types of books available such as References, guides, and step-by-step information.
All the e-books are free to download and new books are being posted when they become available take a look and make sure you bookmark it or add the RSS article feed to your favourite RSS Reader:
If you’ve ever processed a cube then browsed it and there’s no data in there one of the common resolutions is to check that the word ‘CALCULATE’ exists in the Calculations tab within the Cube Designer in BIDS as shown below:
If it doesn’t exist then add and re-process your cube.
An excellent start to using and understanding SQL Server DMV’s and better still it’s available for FREE!!
The pdf book and scripts can be
downloaded from here:
As a minimum every SQL Agent Job should have an operator (or DBA) notification setup ideally for failure or completion.
How do I check which operators are setup against my Agent Jobs I hear you say!
Well run this query below to find out. If you have any agent jobs return as ‘No email notification configured’ in the [Notify_level_email] field then an email notification is not setup and you should look into these as a priority (unless you have other methods of monitoring Agent Jobs that is!)
SELECT SJ.NAME, SO.[name],SO.[email_address],
WHEN '1' THEN 'When the job succeeds'
WHEN '2' THEN 'When the job fails'
WHEN '3' THEN 'When the job completes'
ELSE 'No email notification configured'
msdb.dbo.sysjobs SJ LEFT JOIN [msdb].[dbo].[sysoperators] SO
ON SJ.notify_email_operator_id = SO.ID
ORDER BY SO.ID, SJ.NAME
Finding-which-Operators-are-setup-against-SQL-Agent-Job-Notifications (224 downloads)
The example below shows the operators setup against Agent Jobs and the email address the notification will be sent to, the first row shows that no operator is setup on this job for email notifications, if this job fails no-one may be aware.
There’s a few pre-requisites that your need to ensure are setup first for Agent Email Notifications to work:
1. Database Mail should be configured and working (see this great article on SQL Server Central if you don’t know how to setup Database Mail)
2. Agent Operators should be setup with valid email addresses (see my post on recommended operators to setup)
3. Agent Jobs should have an email notification configured ideally for ‘When jobs the fails’
Database Mail should be configured on your SQL Server, if it isn’t get it done now there are no excuses, this is one of the main methods that can be used for finding out when there are issues with your SQL Server!
It’s a good idea to setup an operator with a distribution email group email address so if a DBA leaves or moves onto another position the distribution group can be easily updated once in Active Directory as opposed to being updated on every SQL Server.
You could also create additional distribution email groups and add them as operators this is useful if other administrators needs to be aware of certain Agent Jobs failing such as say BI Jobs.
We all know that AX2009 uses cursors extensively – simply executing a SQL statement in SSMS doesn’t always result in the same query plan being used that an AX generated SQL statement would use.
Want to run a query in SQL Management Studio exactly how Dynamics AX 2009 runs the query e.g. as a API Cursor so you can ensure you are looking at the same query plan AX has used then run the query below.
Simply replace the red text below with all the query variables and replace the blue text with your parameters and execute the full statement and voila you can view the exact query plan AX uses to execute the statement.
TIP: If your unsure of the variables to declare in the cursor you can find them by doing a SQL Profiler Trace.
— Normal FFO Cursor
— Script taken from AX Dynamics Performance Analyzer scripts by Rod Hansen (http://code.msdn.com/dynamicsperf)
declare @p1 int
declare @p2 int
declare @p5 int
— Fast Forward(16)+Parameterized(4096)+AutoFetch(8192)+AutoClose(16384)
declare @p6 int
declare @p7 int
— Number of Rows for AutoFetch.
— This is calculated by Maximum Buffer Size (24K default) / Row Length
exec sp_cursorprepexec @p1 output,@p2 output,N’@P1 nvarchar(5),@P2 nvarchar(21)‘,N’SELECT A.SALESID,A.RECID FROM SALESLINE A WHERE ((DATAAREAID=@P1) AND (SALESID>@P2))’,@p5 output,@p6 output,@p7 output,N’ceu’,N’SO-100004′
— @p2 contains cursor handle for fetch call
exec sp_cursorfetch @p2,2,1,@p7
Ever wondered what queries are calling what tables or do you have a query and want to see how many times it’s been called (since last plan cache flush) and view the query plan for it.
These queries are a good to use when performance tuning indexes and should be a part of your toolbox. You need to know what queries are using your table before you can start to plan the best indexing strategy for your table.
Note: Query#2 will only work in SQL Server Management Studio 2008 onwards.
Here is a quick and easy way to do this:
This query will show you the plan handle value you need to extract the query plan from the plan cache, it will also show you how many times the query has been executed using that plan handle.
-- Shows SQL plan handles in plan cache for a specific piece of SQL text
SELECT plan_handle, usecounts AS ExecutionCount, st.text
CROSS APPLY sys.dm_exec_sql_text(plan_handle) AS st
WHERE text LIKE N'%<Enter Table name or partial Query here>%'
ORDER BY usecounts DESC
Using the plan_handle value from Query#1 insert it into the where clause and this will give you XML result which you can click on to view the graphical query plan.
-- Show XML query plan ** Only works with SSMS2008 **
sys.dm_exec_query_plan(<Paste Query "plan_handle" value from previous query here>)
Query#1 Results of ‘%EVENTINBOX%’
Query#2 – XML result of plan_handle (0x06001100A23AD31D40031579060000000000000000000000) from highest exection count of Query#1
Query#2 – Visual Query Plan after clicking on XML result show above
New Version Available
A new version of the SQL Server Maintenance Solution by Ola Hallengren’s is now available. The new version supports SQL Server 2012.
One new feature in SQL Server 2012 is AlwaysOn Availability Groups. This feature supports multiple replicas of a database. You can back up any replica and define a preferred backup replica.
SQL Server 2012 also supports online rebuilding of indexes with varchar(max), nvarchar(max), varbinary(max), or XML data types or large CLR types. The new version of the SQL Server Maintenance Solution makes use of this capability.
You can read more about the most recent version of the solution at
or download it at
Updated documentation is now available at
A recent issue I was looking at confused me as we were adding SQL Agent Jobs to run a SSRS report but the agent job was not appearing in the ‘sysjobschedules’ table, eventually the job did appear but why did it not appear straight away.
The reason is the the sysjobschedules table refreshes every 20 minutes, which may affect the values returned by the sp_help_jobschedule stored procedure.
Read the article here on MSDN
It’s useful to use a naming convention on Agent Jobs or Maintenance plans, this ensures they are grouped together and easier to find when you have lots of job or maintenance plans on your SQL Server.
Download (PDF Version)
SQL-Server-Object-Naming-Conventions-Administration (924 downloads)
Download (Word Version)
SQL-Server-Object-Naming-Conventions-Administration (1429 downloads)
Introducing Microsoft SQL Server 2012, by Ross Mistry (@RossMistry) and Stacia Misner (@StaciaMisner), is now available as a free download!
It’s available in the following formats:
PDF , MOBI (Kindle Format), EPUB
Introducing Microsoft SQL Server 2012 includes 10 chapters:
PART I DATABASE ADMINISTRATION (by Ross Mistry)
1. SQL Server 2012 Editions and Engine Enhancements
2. High-Availability and Disaster-Recovery Enhancements
3. Performance and Scalability
4. Security Enhancements
5. Programmability and Beyond-Relational Enhancements
PART II BUSINESS INTELLIGENCE DEVELOPMENT (by Stacia Misner)
6. Integration Services
7. Data Quality Services
8. Master Data Services
9. Analysis Services and PowerPivot
10. Reporting Services