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

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.
====================

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