He is a gifted speaker. Very down to earth but very sharp.
My taek away from the session is that:
1. Make sure there are no sort warnings. If that is happening , the cache is spilling to tempdb.
2. best practices like one file on tempdb per core may be counter producitonve if your streaming is geared for only sequential I/O. If you have SAn infra-structure then it can handle up to 16 streams. ?If that is the case , then we are better off leaving that stuff to SAN Admin to worry about it.
3. By granting slightly more memory thtan required for processing , we can trick teh sql engine to grant more memory that will make the query run faster.
4. We cna do all this watcihg file monitor as to which file it is writing to etc.
Friday, October 29, 2010
Tuesday, December 22, 2009
F/Key scripts
http://www.mssqltips.com/tipprint.asp?tip=1376
-- Enable, Disable, Drop and Recreate FKs based on Primary Key table
-- Written 2007-11-18
-- Edgewood Solutions / MSSQLTips.com
-- Works for SQL Server 2005
SET NOCOUNT ON
DECLARE @operation VARCHAR(10)
DECLARE @tableName sysname
DECLARE @schemaName sysname
SET @operation = 'DROP' --ENABLE, DISABLE, DROP
SET @tableName = 'SpecialOfferProduct'
SET @schemaName = 'Sales'
DECLARE @cmd NVARCHAR(1000)
DECLARE
@FK_NAME sysname,
@FK_OBJECTID INT,
@FK_DISABLED INT,
@FK_NOT_FOR_REPLICATION INT,
@DELETE_RULE smallint,
@UPDATE_RULE smallint,
@FKTABLE_NAME sysname,
@FKTABLE_OWNER sysname,
@PKTABLE_NAME sysname,
@PKTABLE_OWNER sysname,
@FKCOLUMN_NAME sysname,
@PKCOLUMN_NAME sysname,
@CONSTRAINT_COLID INT
DECLARE cursor_fkeys CURSOR FOR
SELECT Fk.name,
Fk.OBJECT_ID,
Fk.is_disabled,
Fk.is_not_for_replication,
Fk.delete_referential_action,
Fk.update_referential_action,
OBJECT_NAME(Fk.parent_object_id) AS Fk_table_name,
schema_name(Fk.schema_id) AS Fk_table_schema,
TbR.name AS Pk_table_name,
schema_name(TbR.schema_id) Pk_table_schema
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id --inner join
WHERE TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName
OPEN cursor_fkeys
FETCH NEXT FROM cursor_fkeys
INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER
WHILE @@FETCH_STATUS = 0
BEGIN
-- create statement for enabling FK
IF @operation = 'ENABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] CHECK CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
END
-- create statement for disabling FK
IF @operation = 'DISABLE'
BEGIN
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] NOCHECK CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
END
-- create statement for dropping FK and also for recreating FK
IF @operation = 'DROP'
BEGIN
-- drop statement
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME
+ '] DROP CONSTRAINT [' + @FK_NAME + ']'
PRINT @cmd
-- create process
DECLARE @FKCOLUMNS VARCHAR(1000), @PKCOLUMNS VARCHAR(1000), @COUNTER INT
-- create cursor to get FK columns
DECLARE cursor_fkeyCols CURSOR FOR
SELECT COL_NAME(Fk.parent_object_id, Fk_Cl.parent_column_id) AS Fk_col_name,
COL_NAME(Fk.referenced_object_id, Fk_Cl.referenced_column_id) AS Pk_col_name
FROM sys.foreign_keys Fk LEFT OUTER JOIN
sys.tables TbR ON TbR.OBJECT_ID = Fk.referenced_object_id INNER JOIN
sys.foreign_key_columns Fk_Cl ON Fk_Cl.constraint_object_id = Fk.OBJECT_ID
WHERE TbR.name = @tableName
AND schema_name(TbR.schema_id) = @schemaName
AND Fk_Cl.constraint_object_id = @FK_OBJECTID -- added 6/12/2008
ORDER BY Fk_Cl.constraint_column_id
OPEN cursor_fkeyCols
FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
SET @COUNTER = 1
SET @FKCOLUMNS = ''
SET @PKCOLUMNS = ''
WHILE @@FETCH_STATUS = 0
BEGIN
IF @COUNTER > 1
BEGIN
SET @FKCOLUMNS = @FKCOLUMNS + ','
SET @PKCOLUMNS = @PKCOLUMNS + ','
END
SET @FKCOLUMNS = @FKCOLUMNS + '[' + @FKCOLUMN_NAME + ']'
SET @PKCOLUMNS = @PKCOLUMNS + '[' + @PKCOLUMN_NAME + ']'
SET @COUNTER = @COUNTER + 1
FETCH NEXT FROM cursor_fkeyCols INTO @FKCOLUMN_NAME,@PKCOLUMN_NAME
END
CLOSE cursor_fkeyCols
DEALLOCATE cursor_fkeyCols
-- generate create FK statement
SET @cmd = 'ALTER TABLE [' + @FKTABLE_OWNER + '].[' + @FKTABLE_NAME + '] WITH ' +
CASE @FK_DISABLED
WHEN 0 THEN ' CHECK '
WHEN 1 THEN ' NOCHECK '
END + ' ADD CONSTRAINT [' + @FK_NAME
+ '] FOREIGN KEY (' + @FKCOLUMNS
+ ') REFERENCES [' + @PKTABLE_OWNER + '].[' + @PKTABLE_NAME + '] ('
+ @PKCOLUMNS + ') ON UPDATE ' +
CASE @UPDATE_RULE
WHEN 0 THEN ' NO ACTION '
WHEN 1 THEN ' CASCADE '
WHEN 2 THEN ' SET_NULL '
END + ' ON DELETE ' +
CASE @DELETE_RULE
WHEN 0 THEN ' NO ACTION '
WHEN 1 THEN ' CASCADE '
WHEN 2 THEN ' SET_NULL '
END + '' +
CASE @FK_NOT_FOR_REPLICATION
WHEN 0 THEN ''
WHEN 1 THEN ' NOT FOR REPLICATION '
END
PRINT @cmd
END
FETCH NEXT FROM cursor_fkeys
INTO @FK_NAME,@FK_OBJECTID,
@FK_DISABLED,
@FK_NOT_FOR_REPLICATION,
@DELETE_RULE,
@UPDATE_RULE,
@FKTABLE_NAME,
@FKTABLE_OWNER,
@PKTABLE_NAME,
@PKTABLE_OWNER
END
CLOSE cursor_fkeys
DEALLOCATE cursor_fkeys
Friday, September 18, 2009
SchemaBinding views...
The error message "Cannot create index on view '*' because the view is not schema bound. (Microsoft SQL Server, Error: 1939)" clearly suggested that in order to create an index on this view I have to make it schema bound, but what is schema binding and how does this work? Schema binding ties an object to the base object that this new object depends upon. So without schema binding if a view is created and the underlying table is changed, the view may break, but the table change can still occur. With schema binding, if the base object is bound to another object, you will not be able to modify the based object unless you drop or alter the object to remove the schema binding.
Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view. "Cannot schema bind view 'dbo.vw_sampleView' because name 'SAMPLETABLE' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."
This error is only generated in case of schema bound views. In the case of ordinary views you will not get this error.
An important point to note is that you must first create a unique clustered index on the view, before you can create any non-clustered indexes. The script below shows us how we can create this unique clustered index or you can do this via SQL Server Management Studio (SSMS).
Create Unique clustered index on view
CREATE UNIQUE CLUSTERED INDEX [TestIndex] ON [dbo].[vw_sampleView] ( [rowguid] ASC ) GO
When this is run the command successfully creates the unique clustered index "TestIndex" on "vw_sampleview". At this point we can add any additional indexes as needed.
Here are some things to consider:
You can not create an index on a view with outer joins used in it, even if you use schema binding
You can not use '*' in the select statement of a view when it is schema bound. In such case you will clearly get error message of level 15 as "Syntax '*' is not allowed in schema-bound objects.".
You will not be able to create clustered index on a view if the view references any nondeterministic functions.
You can not use aggregate functions when using schema binding.
You can not migrate the base table of a schema bound view.
To change the base table we need to remove the SCHEMABINDING option from our objects or drop and recreate these objects once the table has been altered.
On the other hand schema binding does not implement any restriction on the alteration of the view. You may alter or drop the view the same way as you normally would.
Be aware that if you do not use the schema name, (dbo) in this case, then you will get the following error while creating the view. "Cannot schema bind view 'dbo.vw_sampleView' because name 'SAMPLETABLE' is invalid for schema binding. Names must be in two-part format and an object cannot reference itself."
This error is only generated in case of schema bound views. In the case of ordinary views you will not get this error.
An important point to note is that you must first create a unique clustered index on the view, before you can create any non-clustered indexes. The script below shows us how we can create this unique clustered index or you can do this via SQL Server Management Studio (SSMS).
Create Unique clustered index on view
CREATE UNIQUE CLUSTERED INDEX [TestIndex] ON [dbo].[vw_sampleView] ( [rowguid] ASC ) GO
When this is run the command successfully creates the unique clustered index "TestIndex" on "vw_sampleview". At this point we can add any additional indexes as needed.
Here are some things to consider:
You can not create an index on a view with outer joins used in it, even if you use schema binding
You can not use '*' in the select statement of a view when it is schema bound. In such case you will clearly get error message of level 15 as "Syntax '*' is not allowed in schema-bound objects.".
You will not be able to create clustered index on a view if the view references any nondeterministic functions.
You can not use aggregate functions when using schema binding.
You can not migrate the base table of a schema bound view.
To change the base table we need to remove the SCHEMABINDING option from our objects or drop and recreate these objects once the table has been altered.
On the other hand schema binding does not implement any restriction on the alteration of the view. You may alter or drop the view the same way as you normally would.
Thursday, September 10, 2009
Date trims for SSIS expressions
//
//
Adam Jorgensen
Date Expression Compilation in SSIS
By Adam Jorgensen in Adam Jorgensen 09-08-2009 7:52 PM Categories:
Rating: Discuss 484 Reads 484 Reads in Last 30 Days no comments
There are many different ways to manipulate dates when working with them in SSIS. Many great examples have been posted across the web and I use many of them on a regular basis in my coding. It can be difficult however to get a single source for many of these since many of us use the ones we need and move on.
This blog entry (and follow ups) will highlight the most useful date strip/manipulation expressions on the web and link back to their creator/publisher.
Let's dive in !
This wiki over at SQLIS.com does a great job of collecting some of these date scripts. There is also an article published by Darren Green with most of the items in the WIKI. The Wiki is here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html
My goal in this posting is to highlight some cool expressions and encourage you to get engaged and post some of your own.
Some of the coolest and most useful expressions on the WIKI include:
Removing Time from Date:
This is very useful especially when data is coming from non Microsoft systems and has full datetime values everywhere. You may need to do lookups in your ETL against a date dimension and will need to perform this task routinely.
Two good approaches are shown below:
(DT_DATE)(DT_DBDATE)@[User::WorkingDate]
OR
DATEADD("day",DATEDIFF("day",(DT_DBTIMESTAMP)0,GETDATE()),(DT_DBTIMESTAMP)0)
Calculating the beginning and end of the previous month:
Beginning of the Month:
This expression moves three months back from today, subtracting the day count from the current day of month to get the first day of the month. Then it converts the expression to a DT_DBDATE type and then back to a DT_Date to reset the time to 00:00AM.
(DT_DATE)(DT_DBDATE)DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-3,GETDATE()))
End of the Month:
This employs similar logic in reverse, going back to the last day of the preceding month, and then does some data conversions to truncate the time to 00:00AM.
DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-2,GETDATE()))))
If you have an expression (date or otherwise) that makes your life better or development faster, please post it here or email me and I will get it posted with your credits ! :)
Don't forget to post your thoughts or email me your questions to ajorgensen@pragmaticworks.com. As always, this Blog is to help you better understand the tools at your disposal ...
//
Adam Jorgensen
Date Expression Compilation in SSIS
By Adam Jorgensen in Adam Jorgensen 09-08-2009 7:52 PM Categories:
Rating: Discuss 484 Reads 484 Reads in Last 30 Days no comments
There are many different ways to manipulate dates when working with them in SSIS. Many great examples have been posted across the web and I use many of them on a regular basis in my coding. It can be difficult however to get a single source for many of these since many of us use the ones we need and move on.
This blog entry (and follow ups) will highlight the most useful date strip/manipulation expressions on the web and link back to their creator/publisher.
Let's dive in !
This wiki over at SQLIS.com does a great job of collecting some of these date scripts. There is also an article published by Darren Green with most of the items in the WIKI. The Wiki is here: http://wiki.sqlis.com/default.aspx/SQLISWiki/ExpressionDateFunctions.html
My goal in this posting is to highlight some cool expressions and encourage you to get engaged and post some of your own.
Some of the coolest and most useful expressions on the WIKI include:
Removing Time from Date:
This is very useful especially when data is coming from non Microsoft systems and has full datetime values everywhere. You may need to do lookups in your ETL against a date dimension and will need to perform this task routinely.
Two good approaches are shown below:
(DT_DATE)(DT_DBDATE)@[User::WorkingDate]
OR
DATEADD("day",DATEDIFF("day",(DT_DBTIMESTAMP)0,GETDATE()),(DT_DBTIMESTAMP)0)
Calculating the beginning and end of the previous month:
Beginning of the Month:
This expression moves three months back from today, subtracting the day count from the current day of month to get the first day of the month. Then it converts the expression to a DT_DBDATE type and then back to a DT_Date to reset the time to 00:00AM.
(DT_DATE)(DT_DBDATE)DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-3,GETDATE()))
End of the Month:
This employs similar logic in reverse, going back to the last day of the preceding month, and then does some data conversions to truncate the time to 00:00AM.
DATEADD("mi",-1,(DT_DATE)(DT_DBDATE) DATEADD("dd",0,DATEADD("dd",-1 * (DAY(GETDATE())-1),DATEADD("month",-2,GETDATE()))))
If you have an expression (date or otherwise) that makes your life better or development faster, please post it here or email me and I will get it posted with your credits ! :)
Don't forget to post your thoughts or email me your questions to ajorgensen@pragmaticworks.com. As always, this Blog is to help you better understand the tools at your disposal ...
Saturday, July 18, 2009
Partition Schemes - Funcrtion - example script----
--script 1:
USE Master
GO
CREATE DATABASE DBForPartitioning
ON PRIMARY
(NAME='DBForPartitioning_1',
FILENAME=
'c:\PartitionDB\FG1\DBForPartitioning_1.mdf',
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG2
(NAME = 'DBForPartitioning_2',
FILENAME =
'c:\PartitionDB\FG2\DBForPartitioning_2.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG3
(NAME = 'DBForPartitioning_3',
FILENAME =
'c:\PartitionDB\FG3\DBForPartitioning_3.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 )
GO
--script 2:
Use DBFOrPartitioning
GO
-- Confirm Filegroups
SELECT name as [File Group Name]
FROM sys.filegroups
WHERE type = 'FG'
GO -- Confirm Datafiles
SELECT name as [DB File Name],physical_name as [DB File Path]
FROM sys.database_files
where type_desc = 'ROWS'
GO
-- script 3:
Use DBForPartitioning
GO
CREATE PARTITION FUNCTION salesYearPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2009-01-01', '2010-01-01')
GO
--script 4:
Use DBForPartitioning
GO
CREATE PARTITION SCHEME Test_PartitionScheme
AS PARTITION salesYearPartitions
TO ([PRIMARY], FG2, FG3 )
GO
--script 5:
Use DBFOrPartitioning
GO
CREATE TABLE SalesArchival
(SaleTime datetime PRIMARY KEY,
ItemName varchar(50))
ON Test_PartitionScheme (SaleTime);
GO
--Script # 6: Insert sample in horizontally partitioned table
Use DBFOrPartitioning
GO
INSERT INTO SalesArchival (SaleTime, ItemName)
SELECT '2007-03-25','Item1' UNION ALL
SELECT '2008-10-01','Item2' UNION ALL
SELECT '2009-01-01','Item1' UNION ALL
SELECT '2009-08-09','Item3' UNION ALL
SELECT '2009-12-30','Item2' UNION ALL
SELECT '2010-01-01','Item1' UNION ALL
SELECT '2010-05-24','Item3'
GO
--Script # 7: Verify data distribution in horizontally partitioned table
Use DBFOrPartitioning
GO
select partition_id, index_id, partition_number, Rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='SalesArchival'
GO
USE Master
GO
CREATE DATABASE DBForPartitioning
ON PRIMARY
(NAME='DBForPartitioning_1',
FILENAME=
'c:\PartitionDB\FG1\DBForPartitioning_1.mdf',
SIZE=2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG2
(NAME = 'DBForPartitioning_2',
FILENAME =
'c:\PartitionDB\FG2\DBForPartitioning_2.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 ),
FILEGROUP FG3
(NAME = 'DBForPartitioning_3',
FILENAME =
'c:\PartitionDB\FG3\DBForPartitioning_3.ndf',
SIZE = 2,
MAXSIZE=100,
FILEGROWTH=1 )
GO
--script 2:
Use DBFOrPartitioning
GO
-- Confirm Filegroups
SELECT name as [File Group Name]
FROM sys.filegroups
WHERE type = 'FG'
GO -- Confirm Datafiles
SELECT name as [DB File Name],physical_name as [DB File Path]
FROM sys.database_files
where type_desc = 'ROWS'
GO
-- script 3:
Use DBForPartitioning
GO
CREATE PARTITION FUNCTION salesYearPartitions (datetime)
AS RANGE RIGHT FOR VALUES ( '2009-01-01', '2010-01-01')
GO
--script 4:
Use DBForPartitioning
GO
CREATE PARTITION SCHEME Test_PartitionScheme
AS PARTITION salesYearPartitions
TO ([PRIMARY], FG2, FG3 )
GO
--script 5:
Use DBFOrPartitioning
GO
CREATE TABLE SalesArchival
(SaleTime datetime PRIMARY KEY,
ItemName varchar(50))
ON Test_PartitionScheme (SaleTime);
GO
--Script # 6: Insert sample in horizontally partitioned table
Use DBFOrPartitioning
GO
INSERT INTO SalesArchival (SaleTime, ItemName)
SELECT '2007-03-25','Item1' UNION ALL
SELECT '2008-10-01','Item2' UNION ALL
SELECT '2009-01-01','Item1' UNION ALL
SELECT '2009-08-09','Item3' UNION ALL
SELECT '2009-12-30','Item2' UNION ALL
SELECT '2010-01-01','Item1' UNION ALL
SELECT '2010-05-24','Item3'
GO
--Script # 7: Verify data distribution in horizontally partitioned table
Use DBFOrPartitioning
GO
select partition_id, index_id, partition_number, Rows
FROM sys.partitions
WHERE OBJECT_NAME(OBJECT_ID)='SalesArchival'
GO
Sunday, July 12, 2009
SQL Saturday at Birmingham: May 30, 2009
I liked the spirit of hte workshop.
I enjoyed few sessiosn owith real technical inputs and tips. SOme of them I could use in my work situation.
The crowd was loud and distracted by the t-shirts and books being given away...
Will I go next year ? - well probably yes , because there are no other better events in this area....
------------------------------------------------
Andy Warren:
Statistics ----
ASYNC update is recommended if hte stats are getting updated frequently and are heavily used. This way day time response times are not affected.
DOes a full scan 100% sampling
in SQL2k5 , it updates only that needs updating ( 500 updates or 10% of the total )
Always look at the query plan:
With recompile
keep plan
keep fixed plan
optimize for ( 'smith')
Lookat querystats.aspx.
============================
Confio software.
Ex-Oracle guru...Dean Richards
Highest wait times ( Wait types)
Tracing a sessions / process)
User / Batch complaints
Queries performing most I/O - LIO , PIO
Queries consuming CPU
Queries doing full table scans.
known poorly performing SQL
reference - KB822011 - sysprocesses
DMV - dm_exec_query_plan
or profiles event : under Misc: - execution plan
Consider: ERD - Entity Relationship Diagram
DAta Profiler Viewer...
Dan Tow : http://singingsql.com
Book bby him : SQL Tuning ----
-------------------------------------------------------
Chris Eagle:
Reastful DAta
http://www.kodefuguru.com
C R U D ? .... create retrieve update delete.
insert select update delete
post get put delte in HTTP
whistle ????? / $metadata
--------------------------------------------
1. Atomicity - of data
2. Definition of Primary Key
3. Dependency on the primary key
Extremely under-normalized
extrmeley over normalized.
EAV - Entity Attribute Value - this is bad...
big buckets of stuff....
performance considerations ...
metabase - custom database with cross linked to other databases with nesting views ... avoid them if you can since the query optimizer can go nuts ....
Segment the ETL process - where ever you can...
Parsing before deployment.
==============================================================
Brad Marshall - Fuzzy warm SSIS:
Demo on the fuzzy logic.
not very impressive because , the algorithms used for sorting with realtrive weights is not revealed and so not very amenable for tweaking. Waht you see is what you get pretty much.
Fuzzy grouping ; ETRI - error Tolerance Index,
If you want to process it faster, keep simlarity to closer to 1 then it will run faster.
one of the neat features of the new SSIS thingy is the Data Viewer. This give you hte ability to see the results of the fuzzy grouyping you have run recently.
That way, you can always go back and tewak it some more and see if hte results are closer to your expectations and other wise tewak some more kinda routine....
=============================
Paul Randall - He wrote teh DBCC routine on SQL Server.
Tera DAta will scal to several 100 million rows easily.
Power shell.
look at inlude (columns) in the query plans for perfomrance hints.
====================
I enjoyed few sessiosn owith real technical inputs and tips. SOme of them I could use in my work situation.
The crowd was loud and distracted by the t-shirts and books being given away...
Will I go next year ? - well probably yes , because there are no other better events in this area....
------------------------------------------------
Andy Warren:
Statistics ----
ASYNC update is recommended if hte stats are getting updated frequently and are heavily used. This way day time response times are not affected.
DOes a full scan 100% sampling
in SQL2k5 , it updates only that needs updating ( 500 updates or 10% of the total )
Always look at the query plan:
With recompile
keep plan
keep fixed plan
optimize for ( 'smith')
Lookat querystats.aspx.
============================
Confio software.
Ex-Oracle guru...Dean Richards
Highest wait times ( Wait types)
Tracing a sessions / process)
User / Batch complaints
Queries performing most I/O - LIO , PIO
Queries consuming CPU
Queries doing full table scans.
known poorly performing SQL
reference - KB822011 - sysprocesses
DMV - dm_exec_query_plan
or profiles event : under Misc: - execution plan
Consider: ERD - Entity Relationship Diagram
DAta Profiler Viewer...
Dan Tow : http://singingsql.com
Book bby him : SQL Tuning ----
-------------------------------------------------------
Chris Eagle:
Reastful DAta
http://www.kodefuguru.com
C R U D ? .... create retrieve update delete.
insert select update delete
post get put delte in HTTP
whistle ????? / $metadata
--------------------------------------------
1. Atomicity - of data
2. Definition of Primary Key
3. Dependency on the primary key
Extremely under-normalized
extrmeley over normalized.
EAV - Entity Attribute Value - this is bad...
big buckets of stuff....
performance considerations ...
metabase - custom database with cross linked to other databases with nesting views ... avoid them if you can since the query optimizer can go nuts ....
Segment the ETL process - where ever you can...
Parsing before deployment.
==============================================================
Brad Marshall - Fuzzy warm SSIS:
Demo on the fuzzy logic.
not very impressive because , the algorithms used for sorting with realtrive weights is not revealed and so not very amenable for tweaking. Waht you see is what you get pretty much.
Fuzzy grouping ; ETRI - error Tolerance Index,
If you want to process it faster, keep simlarity to closer to 1 then it will run faster.
one of the neat features of the new SSIS thingy is the Data Viewer. This give you hte ability to see the results of the fuzzy grouyping you have run recently.
That way, you can always go back and tewak it some more and see if hte results are closer to your expectations and other wise tewak some more kinda routine....
=============================
Paul Randall - He wrote teh DBCC routine on SQL Server.
Tera DAta will scal to several 100 million rows easily.
Power shell.
look at inlude (columns) in the query plans for perfomrance hints.
====================
Saturday, July 11, 2009
sahrink-files script
USE MASTER GO CREATE TABLE #TMPFIXEDDRIVES ( DRIVE CHAR(1), MBFREE INT) INSERT INTO #TMPFIXEDDRIVES EXEC xp_FIXEDDRIVES CREATE TABLE #TMPSPACEUSED ( DBNAME VARCHAR(50), FILENME VARCHAR(50), SPACEUSED FLOAT) INSERT INTO #TMPSPACEUSED EXEC( 'sp_msforeachdb''use ?; Select ''''?'''' DBName, Name FileNme, fileproperty(Name,''''SpaceUsed'''') SpaceUsed from sysfiles''') SELECT C.DRIVE, CASE WHEN (C.MBFREE) > 1000 THEN CAST(CAST(((C.MBFREE) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' ELSE CAST(CAST((C.MBFREE) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB' END AS DISKSPACEFREE, A.NAME AS DATABASENAME, B.NAME AS FILENAME, CASE B.TYPE WHEN 0 THEN 'DATA' ELSE TYPE_DESC END AS FILETYPE, CASE WHEN (B.SIZE * 8 / 1024.0) > 1000 THEN CAST(CAST(((B.SIZE * 8 / 1024) / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' GB' ELSE CAST(CAST((B.SIZE * 8 / 1024.0) AS DECIMAL(18,2)) AS VARCHAR(20)) + ' MB' END AS FILESIZE, CAST((B.SIZE * 8 / 1024.0) - (D.SPACEUSED / 128.0) AS DECIMAL(15,2)) SPACEFREE, B.PHYSICAL_NAME FROM SYS.DATABASES A JOIN SYS.MASTER_FILES B ON A.DATABASE_ID = B.DATABASE_ID JOIN #TMPFIXEDDRIVES C ON LEFT(B.PHYSICAL_NAME,1) = C.DRIVE JOIN #TMPSPACEUSED D ON A.NAME = D.DBNAME AND B.NAME = D.FILENME ORDER BY DISKSPACEFREE, SPACEFREE DESC DROP TABLE #TMPFIXEDDRIVES DROP TABLE #TMPSPACEUSED
After copying and pasting the above into a SQL Server 2005 query window and running all of the code the result set will look something like this:
Next Steps
With a little modification, you can schedule the script to shrink data files containing over X amount of free space on a drive with less than X amount of disk space.
In a perfect world, DBA’s would just be able to ask for more drive space and not have to worry about shrinking files. It is more efficient to give the database the room it needs instead of having to shrink them all the time. Until then, I guess I will keep using this script.Readers Who Read This Tip Also Read
How to setup SQL Server alerts and email operator notifications
Methods to determine the status of a SQL Server database
Reading the SQL Server log files using T-SQL
Collecting SQL Server database usage information for free space and trending
Listing SQL Server Object Dependencies
More... Comment or Ask Questions About This Tip
Discuss this tip: http://blogs.mssqltips.com/forums/t/628.aspx
There are 1 comments for this tip, last post by tosscrosby
After copying and pasting the above into a SQL Server 2005 query window and running all of the code the result set will look something like this:
Next Steps
With a little modification, you can schedule the script to shrink data files containing over X amount of free space on a drive with less than X amount of disk space.
In a perfect world, DBA’s would just be able to ask for more drive space and not have to worry about shrinking files. It is more efficient to give the database the room it needs instead of having to shrink them all the time. Until then, I guess I will keep using this script.Readers Who Read This Tip Also Read
How to setup SQL Server alerts and email operator notifications
Methods to determine the status of a SQL Server database
Reading the SQL Server log files using T-SQL
Collecting SQL Server database usage information for free space and trending
Listing SQL Server Object Dependencies
More... Comment or Ask Questions About This Tip
Discuss this tip: http://blogs.mssqltips.com/forums/t/628.aspx
There are 1 comments for this tip, last post by tosscrosby
Subscribe to:
Posts (Atom)