Friday, July 5, 2013

[MS SQL Server] Size of auto-created statistics objects in a database

[MS SQL Server] Size of auto-created statistics objects in a database

Size of auto-created statistics objects in a database

Posted: 04 Jul 2013 09:33 AM PDT

Is there a way to calculate the size (in MB) of auto-created statistics objects in a database?I would like to get an idea of the space savings I would get from dropping duplicate statistics;ie. single-column auto-created stats named "_WA_Sys_..." defined on the same column as the leading key of a non-clustered index.What is the physical footprint of these objects in a db?Here is my first attempt, but this does not give me the size of these stats objects:[code="sql"]SELECT s.name, ps.used_page_count * 8192 / (1024 * 1024) AS Size_MBFROM sys.dm_db_partition_stats psINNER JOIN sys.stats sON ps.[object_id] = s.[object_id]WHERE s.auto_created = 1ORDER BY ps.used_page_count DESC;[/code]

How to Check Partition is Working or not

Posted: 04 Jul 2013 07:40 PM PDT

Hi All,In a database (partitionDB) I have created partition then I have crated table on that DB. In another database (Partitiondb_New) I have the same table but here is no partition. In both the datbases there is a table 'Orders'.I have used the following query to partition the database (partitionDB)[quote]Create PARTITION FUNCTION PFORDERDATERANGE (DATETIME)ASRANGE RIGHT FOR VALUES ('2011-05-04','2011-08-04','2011/11/04','2013/01/04','2013/04/04','2013/07/04')Create Partition Scheme PSOrderDateChange as Partition PFORDERDATERANGE to (FG1, FG2, FG3, FG4, FG5, FG6, [Primary])Create table dbo.orders(Orderid int identity (1,1) not null,[Orderdate] [datetime] not null,[Name] nvarchar (255),[Productid] int null,constraint [pk_orders] primary key clustered ( Orderid ASC, Orderdate ASC )ON PSOrderDateChange (OrderDate))on PSOrderDateChange (OrderDate)[/quote]Lastly I have checked to see which filegroups contain how may records[quote]select $partition.PFORDERDATERANGE (o.orderdate) as [Prtition Number],Min(o.orderdate) as [Min Value],max (o.orderdate) as [Max Value],Count(*) [records in Partition]from Orders oGroup by $partition.PFORDERDATERANGE (o.orderdate)order by [Prtition Number][/quote]and I am getting result. to see the result and performance issue I ran a query in both the databases[quote]select * from dbo.Orderswhere Orderdate between '2013-01-04 00:00:00.000' and '2013-04-03 00:00:00.000'[/quote]but in both the scenario I am getting output in 8 secs....then How will I use partition in my database or table?I am sure someone will show the right direction....Thanks in advance

uninstall sql instance in cluster

Posted: 04 Jul 2013 11:25 PM PDT

Received below error while uninstall sql instance in 2 node failover cluster.Error :The selected instance is clustered and cannot be removed as specified.To re remove the selected instance , select "Remove Node" on the Installation center or specify /Action=RemoveNode from the command-line.if i select 'Remove node' then the node will be removed from the failover cluster ?
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog