I used Red Gate Trial Version to Generate my SQL server database Documentation and I Need to remove the automatic generated Header and Footer by the software and this saved my day.It isnt that hard to be a DBA. 🙂
I used following in the VBA and saved my self from 2000 page document.
Sub RemoveHeadAndFoot() Dim oSec As Section Dim oHead As HeaderFooter Dim oFoot As HeaderFooter For Each oSec In ActiveDocument.Sections For Each oHead In oSec.Headers If oHead.Exists Then oHead.Range.Delete Next oHead For Each oFoot In oSec.Footers If oFoot.Exists Then oFoot.Range.Delete Next oFoot Next oSec End Sub
This following query gives you
list of all the indexes
,the tables that they reside in
,how many times they’re been searched
,how many times they’ve been updated
,and the size of the indexes in memory.
SELECT sch.name + '.' + t.name AS [Table Name], i.name AS [Index Name], i.type_desc, ISNULL(user_updates,0) AS [Total Writes], ISNULL(user_seeks + user_scans + user_lookups,0) AS [Total Reads], s.last_user_seek, s.last_user_scan , s.last_user_lookup, ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0) AS [Difference], p.reserved_page_count * 8.0 / 1024 as SpaceInMB FROM sys.indexes AS i WITH (NOLOCK) LEFT OUTER JOIN sys.dm_db_index_usage_stats AS s WITH (NOLOCK) ON s.object_id = i.object_id AND i.index_id = s.index_id AND s.database_id=db_id() AND objectproperty(s.object_id,'IsUserTable') = 1 INNER JOIN sys.tables AS t WITH (NOLOCK) ON i.object_id = t.object_id INNER JOIN sys.schemas AS sch WITH (NOLOCK) ON t.schema_id = sch.schema_id LEFT OUTER JOIN sys.dm_db_partition_stats AS p WITH (NOLOCK) ON i.index_id = p.index_id and i.object_id = p.object_id WHERE (1=1) --AND ISNULL(user_updates,0) >= ISNULL((user_seeks + user_scans + user_lookups),0) --shows all indexes including those that have not been used --AND ISNULL(user_updates,0) - ISNULL((user_seeks + user_scans + user_lookups),0)>0 --only shows those indexes which have been used --AND i.index_id > 1 -- Only non-first indexes (I.E. non-primary key) --AND i.is_primary_key<>1 -- Only those that are not defined as a Primary Key) --AND i.is_unique_constraint<>1 -- Only those that are not classed as "UniqueConstraints". ORDER BY [Table Name], [index name]
Source : Internet
I am feeling exactly the same as Rob Farley must have felt 🙂
© 2011 Rob Farley
My query sucks – it takes too long
So long I wrote this song
The plan’s not big – it ain’t a giant
And yet I have an angry client
Performance now has made her weary
So I’ve come in to fix her query
I promise I won’t ever fail her
Say “Trust me, love, I’m from Australia!”
I need to find you
But I don’t want to search every row
My predicate’s residual
My seek just runs too slow
I thought I’d caught a glimpse of you
Been searching for all day
But all along, I’d done it wrong
I should’ve looked the other way
A trace is on, I know the reads
That fetch the bytes the query needs
There’s spooling from a CTE
They’ve got recursion needlessly
I need to dig a little further
I worry there might be a cursor
The DBA has the plan_handle
He says it’s not corrupt, he knows Paul Randal!
There is an index covering predicates with keys
But my developer has used inequalities
There is a range scan
Repeat chorus x2
I should’ve looked the other way
When you use Table Valued Parameters,TVP, SQL Server internally uses temp table to store the data. Temp tables can be cached for re-used. Caching reduces contentions such as page latch contentions on system tables which can occur as temp tables are created and dropped at a high rate.
If you use TVP with a stored procedure, temp table for the TVP will be cached in SQL Server 2008 while using TVP.
But if you use TVP together with parameterized queries, temp tables for TVP won’t be cached in SQL 2008 or 2008 R2. This leads to page latch contentions on system tables.
We all use COUNT() to count the total records in a table.But there are few facts we all unknowingly ignore.
In the example below I have use COUNT(*), COUNT([tableField]) and COUNT(1)
COUNT(*) does return the count of all the records, BUT takes lot of memory and time when we have a big table with huge data.
COUNT([tableField]) does return the count but when the field has NULL as data value the,it escapes to count those records because the field is NULL.
So always use the Key column.
SO LETS USE
Here 1 = the first column of the table-key column which is always the key column of any table
1) It takes into account the NULL value.
2) It saves time and memory.
Create table tblCountTest
( fldCountryName varchar(10));
insert into tblCountTest values (‘Nepal’);
insert into tblCountTest values (‘China’)
insert into tblCountTest values (NULL);
insert into tblCountTest values (”);
insert into tblCountTest values (‘Cambodia’);
insert into tblCountTest values (NULL);
insert into tblCountTest values (‘Japan’);
insert into tblCountTest values (‘Brazil’);
select * from tblCountTest
select COUNT(*) as recordCount FROM tblCountTest; — shows recordCount = 8
select COUNT(fldCountryName) as recordCount FROM tblCountTest; — shows recordCount = 6
select COUNT(1) as recordCount FROM tblCountTest; — shows recordCount = 8
Happy Coding 🙂
I just got a notification from google “Google Terms of Service Updated” and I am having an mini anxiety attack.I am wondering who will be scrutinizing my information, my emails, my stuffs, how will the data be used, abused and sold and resold.I wish I could declare,ok I wont use this thing called internet,I will shut down everything.Even-if I decide to,I am already on (their) data-centers,a considerable amount of me is already with them.And I cannot tell them I don’t want to use you please spare me.
I am all scattered in web, part of me lies in facebook other half on twitter and I am tits and bits all over in different social networking sites.A chunk of me lies in sugarsync,dropbox,where not.Yes I don’t feel secure, there are cloud servers I use, I have had synced me with them, my personnel belongings, professional journals, my secret indulgings what not.Yes I am scattered everywhere.I so am floating in fearful concern of my interest,Phew!!
I have been having this feeling way before Mr Snowden leaked Prism.I sometimes wonder if using TOR or disconnect will save me from being crawled and tracked to some extent. Again I question Would not these so called anti surveillance or anti tracking companies use my informations.Would not it be same in one way or other?I let other bunch of them use me, just in different way.
when I read about the Silk Road, it was so intriguing;I wondered how things worked and fantacise of building something similar.Wont it be amazing to have something of my own: no surveillance, no crawling, no bots, a world of my own, where my data would be safe and not employed for filling up other’s pocket
Nothing is for free I get it,so this is the sum amount I need to pay for using things for free but isn’t this amount we are paying a bit too much? I am solding myself off to get a bar of candy for free.This is an abuse.We are being abused and this has lead each one of us to live in threat, each single moment. Some dignitaries live in fear-fear of being public,affairs being barefaced.And a normal person like me fear of being tagged as a republican or supporter of Mao ,Zeehad or anything, on basis of the conversations I make on instant messaging or my emails or social media activities which is absurd.I like Manchester United.Will that make me a anti Chelsea supporter when I am teasing my friend with anti Chlesea slogans?
I think we all need to wake up and start thinking about these issues.
How can we live like this?
This is against my rights to security, rights to liberty,my rights to live a life.