Statistics revisited

Currently, i am working on a short project. In SQL Server 2000 a intensively used table is used with a lot of columns and there a lot of indexes created on this table(about 80). On top of that SQL Server 2000 has used all the 'slots' of the indexes for Statistics. In 2005 and 2008 indexes and statistics are seperated but in 2000 it isn't.

I've searching for this quite long and i found on Kimberly Tripp site (http://www.sqlskills.com/blogs/Kimberly/post/Indexes-in-SQL-Server-20052008-Best-Practices-Part-1.aspx#ixzz0kP811y1Z) a confirmation for this. The vendor wants to add indexes on this table but can't because the index slots are being used by the statistics.

So this mean:
Clustered index                     1
Non clustered index            80
Statistics used by the index  81
Totaal                               162

And there is a maximum of 250 indexes/statistics in SQL Server 2000 and this would mean there 250-162 = 88 statistics being used and we have to determine whether they should be dropped. How? i haven't found out yet. When i found the answer i'll come back to blog about it.

Passing a parameter From a SSIS package to a storedprocedure and back again

Currently i'm developing a template package in SSIS for a new project. One thing that's important in my opinion is running package in an atomic way and his own environment (variables). So passing variables from a parent package to a child package is a no go for me. This would mean that you have to run the parent and the child to get a correct value of the variable. You can't run the package correctly when executing this separately. When running a lot packages you can determine a batchnumber once and pass it through from parent to child but this is a no goner (as is said)

So this is the case: i have a package in which i extract a table (Person) into a Import database and i want to log a batchnumber with every record, every logging and every error that could occur. So i created  SQL Task in which i execute a stored procedure and pass a tablename to the stored procedure and retrieve a proces run number back. This is the result

Below a screen dump of the SSIS Parameter mapping of the SQL Task:

And below you see the variable declarations

And this is the T-SQL code:

DECLARE @RC int

DECLARE @chvParDatabase varchar(100)
DECLARE @chvParTabelNaam varchar(100)
DECLARE @intParProcesNummer int
DECLARE @intProcesNummer int

EXECUTE @RC = [MetaServices].[dbo].[uspMetaServices_GetProcesnummer] ,?, ? OUTPUT

The problem with stored procedures, SSIS and the expression language it's hard to match the datatypes unfortunately. For instance the datatype in T-SQL is Integer, in SSIS int32 and in expression language is it a Long datatype. I tried the integerdatatypes in the parameter mapping screen but errors were my part.

Greetz,
Hennie

SQL Server 2008 R2 Update for developers training kit required software

Today i downloaded the SQL Server 2008 R2 Update for developers training kit (http://www.microsoft.com/downloads/details.aspx?familyid=FFFAAD6A-0153-4D41-B289-A3ED1D637C0D&displaylang=en) and installed it into a virtual environment. All kinds of prerequisites, Beta's , Release candidates, tools were asked to install. Below you'll see the list of software i installed and the link to download the software:

• Reportbuilder 3.0 (http://www.microsoft.com/downloads/details.aspx?FamilyID=f78b6b1e-8ccb-407a-bc3e-7955d60e1a6c&displaylang=en).
• Powerpivot for excel (http://www.powerpivot.com/download.aspx).
• 32 bits
• 64 bits
• Helpfiles
• Streaminsight CTP2 (http://www.microsoft.com/downloads/details.aspx?FamilyID=A3FAA562-B6DC-4702-90C6-BF8E08DF3B8B&displaylang=en&displaylang=en).
• Adventureworks R2 (http://msftdbprodsamples.codeplex.com/releases/view/24854).
• Microsoft office 2010 Beta  (http://www.microsoft.com/office/2010/en/download-office-professional-plus/default.aspx).
• Sharepoint server 2010 Beta (http://www.microsoft.com/downloads/details.aspx?FamilyID=77c30c6c-47fc-416d-88e7-8122534b3f37&displaylang=en).
• Visual Studio 2010 Beta (http://msdn.microsoft.com/en-us/vstudio/dd582936.aspx)

Quite a bunch of software! I think that i listed all the required software for using in the SQL Server 2008 R2 Update for developers training kit but may be i forgot one.

Greetz
Hennie

SQL Server 2008 statistics

Today i want to tell you something about statistics in SQL Server 2008. Microsoft SQL Server 2008 collects statistical information about indexes and column data stored in the database. These statistics are used by the SQL Server query optimizer to choose the most efficient plan for retrieving or updating data (BOL). While in the majority of cases SQL Server takes care of statistical housekeeping automatically, it’s useful to have some understanding of what’s going on when you have to intervene.


SQL Server collects the following information:
• The number of rows and pages accupied by a table’s data
• The time that statistics were last updated
• Average length of keys in a column
• Histograms showing the distribution of data
• String summaries thart are used when performaing LIKE queries.

Suppose you have an column with following numbers Anderson, Andersen,Smith,Williams, Zhang. An histogram would look like this :

Andersen     2
Smith        1
Williams     1
Zhang        1

Suppose that you are searching for a value Anderson, Smith and Williams then the estimation, based on the above example would be 4 rows. This about 80% total rows that match the criteria. This would mean that SQL Server is going to scan the table fully instead of using the index (off course in a real world example you need more data).

I used the following code from MSDN :

-- Clean up objects from any previous runs.
IF object_id(N'Person.Contact','U') IS NOT NULL
DROP TABLE Person.Contact
GO

IF EXISTS (SELECT * FROM sys.schemas WHERE name = N'Person')
DROP SCHEMA Person
GO

-- Create a sample schema and table.
CREATE SCHEMA Person
GO

CREATE TABLE Person.Contact(
FirstName nvarchar(60),
LastName nvarchar(60),
Phone nvarchar(15),
Title nvarchar(15)
)
GO

-- Populate the table with a few rows.
INSERT INTO Person.Contact
VALUES(N'John',N'Smith',N'425-555-1234',N'Mr')
INSERT INTO Person.Contact
VALUES(N'Erik',N'Andersen',N'425-555-1111',N'Mr')
INSERT INTO Person.Contact
VALUES(N'Erik',N'Andersen',N'425-555-3333',N'Mr')
INSERT INTO Person.Contact
VALUES(N'Jeff',N'Williams',N'425-555-0000',N'Dr')
INSERT INTO Person.Contact
VALUES(N'Larry',N'Zhang',N'425-555-2222',N'Mr')
GO
-- Show that there are no statistics yet on the Person.Contact table.
sp_helpstats N'Person.Contact', 'ALL'
GO

-- Implicitly create statistics on LastName.
SELECT * FROM Person.Contact WHERE LastName = N'Andersen'
GO
-- Show that statistics were automatically created on LastName.
sp_helpstats N'Person.Contact', 'ALL'
GO

--DROP STATISTICS Person.Contact._WA_Sys_00000002_7D78A4E7

--Same Histograms.
DBCC SHOW_STATISTICS(N'Person.Contact', _WA_Sys_00000002_7D78A4E7)
--DBCC SHOW_STATISTICS(N'Person.Contact', IX_Contact_Lastname)

Below you can see an example of the histogram:
 

SQL Zaterdag

Last Saturday i went to ‘SQL Zaterdag’ seminar and this new seminar is great way of being informed about the (new) features of SQL Server. This seminar was organized by SQL Pass.nl, Infosupport and Microsoft Netherlands. There were two tracks: one BI track and a Not BI track:Engine track. I joined the BI track with an old co worker. These were the subjects of the BI track:

• SQL Server – de BI release,
• Master Data Management,
• SSIS : Tuning for Performance 
•  Building a dashboard with Powerpivot, Reporting Services and Performancepoint .

I would like to share some interesting stuff:

BI release (Hans Geurtsen)

• The release date of SQL Server 2008 R2 is may 2010.
• There are really neat features in Reporting services specially for supporting the Selfservice BI strategy of Microsoft. Report builder 3.0 will aid the power user more and more with self supporting functionality.
• Now you have shared datasets which you can share between reports. So i can imagine that a Power user or developer build a query and when it's authorized and approved it can be shared between reports. So this will lead to less double queries if adopted in a proper way.
• Shared report parts for mash up support. Mash up technologie will be a technology which will emerge in the coming years. Building in the right way organisations will be more agile because report parts can be created, dropped and changed easily. Another aspect is that when reports use the same reports parts they can be updated with the new report part. But updating the reports in a push strategy is not going to work (at this moment). You can't update all the reports all at once. Yoú'll have to load the reports and refresh the report parts manually.
• 2 extra pilars in the BI stack for Master Data management (stratature) and for Streaminsight (CEP).
• Adjustments in the SSAS : Classic OLAP and Powerpivot support.
• A new edition : Parallel datawarehousing (DatEllegro) .

Master data management (Marcel Westra)

Applications needs to be adjusted for supporting the MDM because data needs to be pushed to the MDM hub and down to the other worker hubs. So it’s gonna take a while before a successful MDM project is implemented. But when this implemented in the right way (?) the source systems will transform into a fact generator. This will have a great benefit in dataquality and will have consequences for implementing and maintaining datawarehouses.

SSIS : Tuning for performance (James Rowland-Jones):
James explained all kind of performance issues with SSIS. Stuff i haven't seen before like the log events screen. I have to look into that. Also he spoke about jumboframes(?). Below you see point that i managed to grab during his fast speed speech:

• A lot of performance counters which can aid tuning. Too many to write down here. These are familiar and can also be found in the Microsoft certification books.
• Some discussion about properties which can aid performance like FastParse.
• Make a baseline when importing data. (use the trash destination).
• Don’t use ADO.net for your source and destination. It will generate procedural based loops. Used OLEDB or SQL destination.

Building a dashboard with powerpivot, Reporting services and sharepoint (Kasper de Jonge)

Kasper showed great features with reporting, powerpivot and sharepoint. It seems to me that Powerusers, developers, etc will built components with reporting and powerprivot and than they will be uploaded to sharepoint and can be used by the users. Kasper showed all kind of possibilities and I was impressed by the new features. This demo show the integration of powerpivot, excel, reporting services and sharepoint in a great way.

Greetz,
Hennie