Search results matching tags '2005' and '2008'

Power Pivot SQL Agent Job Monitor

manowar — Mon, 07 Jan 2013 21:23:29 GMT

In the last days I needed a quick and efficient way to monitor the performance of a job and of each step over time. There’s a lot of interesting data in MSDB database and the various sysjob tables, but the stored data is not really easy to use outside the Management Studio. Date and Time are not stored as date/time data type, there is no easy way to correlate the performance of a step with the related job execution (for example is not that easy to return all the steps of a job executed, say, yesterday at 10:00).

So I decided to write some views to make the querying easier and then the next move was to create a Tabular Model with Power Pivot on Excel to make also analysis easier.

Since this is a somehow recurring task that I find myself doing during my job – not often, but when I have to do it now I have something much more powerful in my hands – I decided to put it also con CodePlex so that that little work can be shared with the community.

You can find the scripts with the objects (3 UDFs and 4 Views) that must be created inside MSDB and the Power Pivot Excel 2010 model here:

http://jobmonitor.codeplex.com/

Enjoy!

SYS2 scripts updated (December 2012)

manowar — Mon, 12 Dec 2011 22:26:43 GMT

I’ve updated my SYS2 scripts:

Added a new script to see how much buffer cache memory is used by each database
Updated the sys2.stats script in order to have only one row per statistics
Updated the sys2.query_stats script to use the sys.dm_exec_plan_attributes dmv to get better information on which database was used by the cached plans

As usual they are available from CodePlex:

http://sys2dmvs.codeplex.com/

Enjoy!

Spotlight session at PASS 2011 - Temporal Snapshot Fact Table

manowar — Sun, 29 May 2011 13:44:59 GMT

I’m very happy to announce that my proposal for the Spotlight session I’ve been invited to deliver at PASS 2011 has been accepted!

Temporal Snapshot Fact Table

You are designing a BI Solution and your customer ask you to keep a snapshot of the status of all their documents (orders, insurances, contracts, bills...whatever the word "document" may mean) for all the days of the year. They have millions of documents and they want to have in their Data Warehouse all the data they have gathered right from the very first operating day.

If you have 1 million of documents (on average) and you have to keep a snapshot of them for each one of the 365 days in a year, and you have 10 year of history, you're going to have a 3 billions table just to start with. That's a very big and challenging number, and you may have not the option to buy a Parallel Data Warehouse.

In this session, we'll see how we can turn the usual snapshot tables into temporal table so that we can store time intervals in order to avoid data duplication, while keeping the Data Warehouse design usable by Analysis Services (that doesn't know what an interval is) and optimizing it to have very good performance even on standard hardware.

The explained technique is a result of several month of research and has been applied to the Data Warehouse of an insurance company where we had to deal with two times the number said before.

The topic is very interesting and – I bet – very intriguing for many people working in BI and specially with Analysis Services, since it lacks the support of “time intervals” to define the validity period of a fact row. With my SolidQ Italian collegues we’ve been able to find a way to overcome this limitation, allowing the storage of daily snapshots of data with a very high efficency and performance.

In this session I’m going to share everything we discovered with you. It will be really interesting, I can tell you! Probably one of the most advanced – yes simple - usage of SSAS and Many-To-Many relationship you’re going to see.

SYS2 Scripts Updated – Scripts to monitor database backup, database space usage and memory grants now available

manowar — Sun, 13 Feb 2011 17:30:22 GMT

I’ve just released three new scripts of my “sys2” script collection that can be found on CodePlex:

Project Page: http://sys2dmvs.codeplex.com/

Source Code Download: http://sys2dmvs.codeplex.com/SourceControl/changeset/view/57732

The three new scripts are the following

sys2.database_backup_info.sql
sys2.query_memory_grants.sql
sys2.stp_get_databases_space_used_info.sql

Here’s some more details:

database_backup_info

This script has been made to quickly check if and when backup was done. It will report the last full, differential and log backup date and time for each database. Along with these information you’ll also get some additional metadata that shows if a database is a read-only database and its recovery model:

By default it will check only the last seven days, but you can change this value just specifying how many days back you want to check.

To analyze the last seven days, and list only the database with FULL recovery model without a log backup

select * from sys2.databases_backup_info(default)
where recovery_model = 3 and log_backup = 0

To analyze the last fifteen days, and list only the database with FULL recovery model with a differential backup

select * from sys2.databases_backup_info(15)
where recovery_model = 3 and diff_backup = 1

I just love this script, I use it every time I need to check that backups are not too old and that t-log backup are correctly scheduled.

query_memory_grants

This is just a wrapper around sys.dm_exec_query_memory_grants that enriches the default result set with the text of the query for which memory has been granted or is waiting for a memory grant and, optionally, its execution plan

stp_get_databases_space_used_info

This is a stored procedure that list all the available databases and for each one the overall size, the used space within that size, the maximum size it may reach and the auto grow options. This is another script I use every day in order to be able to monitor, track and forecast database space usage.

As usual feedbacks and suggestions are more than welcome!

BI Virtual Chapter: “Adaptive BI: Engineering a BI Solution”

manowar — Thu, 09 Sep 2010 09:54:27 GMT

On the next 20th Semptember I’ll deliver a session on a topic that is IMHO really important but is not yet covered enough:

Adaptive BI: Engineering a BI Solution

Are you starting to create a BI solution....but where do you begin with? How to setup everything correctly so that you'll be able to handle new features required by the customers easily and without too much effort? Which standard do you put in place so that everyone who will join the project will be immediately operative? How do you define your BI architecture so that it can be sound and flexible and the same time? All these questions come out every time someone has to start a BI project. In this session I'll present a set of standard rules we use to create our BI project, consolidated in more than five years of development (since SQL 2005 was released) and that allow to put some technical rules that helps you to start in the correct way right from the beginning: naming convention, architecture decisions, database unit testing, layering and everything you need to know to build an "Adaptive" Business Intelligence solution. The idea is to be flexible in terms of architectural decision but have some well-known rules in the solution to make it “engineerable”: in one word..."Adaptive"!

If you’re into BI this session can give you some interesting ideas on how to structure your next project. See you in cyberspace here

https://www.livemeeting.com/cc/usergroups/join?id=C97PWB&role=attend&pw=Z%40%2B%5Ew%2B%40h4

on Sept 20th at 12pm EST.

At the end of the session I’ll show also how the rules can be applied automatically with the right tools, creating an entire package to load a dimension in less then 1 minute, with all best pratices and standard in place!

DTLoggedExec 1.0 Stable Released!

manowar — Sun, 09 May 2010 08:51:43 GMT

After serveral years of development I’ve finally released the first non-beta version of DTLoggedExec! I’m now very confident that the product is stable and solid and has all the feature that are important to have (at least for me).

DTLoggedExec 1.0
http://dtloggedexec.codeplex.com/releases/view/44689

Here’s the release notes:

FIRST NON-BETA RELEASE! :)
Code cleaned up
Added SetPackageInfo method to ILogProvider interface to make easier future improvements
Deprecated the arguments 'ProfileDataFlow', 'ProfilePath', 'ProfileFileName'
Added the new argument 'ProfileDataFlowFileName' that replaces the old 'ProfileDataFlow', 'ProfilePath', 'ProfileFileName' arguments
Updated database scripts to support new reports
Split releases in three different packages for easier maintenance and updates: DTLoggedExec Executable, Samples & Reports
Fixed Issue #25738 (http://dtloggedexec.codeplex.com/WorkItem/View.aspx?WorkItemId=25738)
Fixed Issue #26479 (http://dtloggedexec.codeplex.com/WorkItem/View.aspx?WorkItemId=26479)

To make things easier to maintain I’ve divided the original package in three different releases. One is the DTLoggedExec executable; samples and reports are now available in separate packages so that I can update them more frequently without having to touch the engine.

Source code of everything is available through Source Code Control:

http://dtloggedexec.codeplex.com/SourceControl/list/changesets

As usual, comments and feebacks are more than welcome! (Just use Codeplex, please, so it will be easier for me to keep track of requests and issues)

SYS2 DMVs on CodePlex

manowar — Mon, 18 Jan 2010 18:09:03 GMT

I’ve just created a new project on CodePlex anmed SYS2DMVS, where I’ll put all my sys2 scripts so that they can be found and accessed more easily. Also it will more easy for me to maintain them, just a Check-In with SVN and that’s it :-).

http://sys2dmvs.codeplex.com/

The scripts are growing in number, they are now 9, so I though that they deserve a better place to stay than my personal website.

This also has another important meaning: if you want to contribuite, I’ll be *very* *very* happy to have you on board for this project. I think it would be very nice and useful if we can make this project grow all togheter.

So, if you have a script to share, and you have the will to make it adhere to a certain style (I followed the DMVs style: lower case name, using “_” to separate names, and so on) and to share your own scripts with the community, you’re more than welcome!

sys2 scripts updated

manowar — Thu, 07 Jan 2010 20:54:10 GMT

I’ve updated my “sys2” scripts with three 3 new scripts:

sys2.objects_dependencies
A wrapper around sys.sql_expression_dependencies that shows also related informations taken from sys.object table, like object name, object type and schema name of the referencing entity.

sys2.objects_partition_ranges
Shows information on partitioned table like rows per partition, partition ranges and partition filegroup destination.

sys2.objects_data_spaces
List tables and indexes and show in which filegroup they reside.

They are freely available – along with all the others – here:

http://www.davidemauri.it/resources/sql-scripts.aspx

Enjoy!