Ms Sql Server Technology Discussions

[deals2buy]

[quote name='Desamudhuru' timestamp='1352300315' post='1302757441']
chusa bhayya kani answer teliyaka musukuna
[/quote]
thats ok my boy..answer teleekapothe teledu ani dhairyam ga cheppu vachi

[Desamudhuru]

[quote name='deals2buy' timestamp='1352305579' post='1302757841']
thats ok my boy..answer teleekapothe teledu ani dhairyam ga cheppu vachi
[/quote]

[deals2buy]

[quote name='Desamudhuru' timestamp='1352310077' post='1302758413']

[/quote]
oka question esanu chudu pakka thaadu lo

[Desamudhuru]

[quote name='deals2buy' timestamp='1352310156' post='1302758421']
oka question esanu chudu pakka thaadu lo
[/quote]
Chustanu baa 1 min.

[deals2buy]

Partitioned Tables, Indexes and Execution Plans: a Cautionary Tale

http://www.simple-talk.com/sql/database-administration/partitioned-tables,-indexes-and-execution-plans-a-cautionary-tale/

[deals2buy]

[b] [url="http://www.sqlservercentral.com/blogs/chadmiller/2012/11/05/cloning-sql-servers-to-a-test-environment/"]Cloning SQL Servers to a Test Environment[/url][/b]


http://www.sqlservercentral.com/blogs/chadmiller/2012/11/05/cloning-sql-servers-to-a-test-environment/

[deals2buy]

[b]Today's Question:[/b]

The following code shows three methods of selecting the number (to the nearest 100) of nanoseconds since midnight. Which, if any, will work reliably?
--Method 1:
-- use datediff with NS as the datepart parameter
select DATEDIFF(NS,
dateadd(D,datediff(D,0,sysdatetime()),0),SYSDATETIME())

--Method 2:
--use the internal structure of datetime(2)
cast(cast(reverse(substring(cast(@t as binary(9)),2,5))
as binary(5)) as bigint)*100

--Method 3:
-- go via a character string conversion
-- to ISO 8601 with nanoseconds format
select cast (substring(convert(
nchar(29),sysdatetime(),129),21,9) as bigint)

--Method 4:
-- do it piecemeal with several datediffs
with now(t) as (
select SYSDATETIME() as t
),dy (d) as (
select dateadd(D,datediff(D,0,now.t),0) as d from now
),
secs (ss) as (select datediff(S,dy.d,now.t) from now cross join dy),
secstart(start) as (select DATEADD(S,secs.ss,dy.d)from secs cross join dy)
select 1000000000 * CAST(secs.ss as bigint)+CAST(datediff(NS,secstart.start,t) as bigint)
from secs cross join dy cross join secstart cross join now

[deals2buy]

Interview Question:

What stored procedure is used to enable Database Mail in SQL Server 2012?
[b]Answer: [/b]sp_configure

[deals2buy]

[b] Smart Index Rebuild - All Databases[/b]


/*
SCRIPT NAME Smart Index Rebuild - All Databases
DATE Oct 2012
DEVELOPER Tim Hollobon
HISTORY Original #fraglist table code created by Microsoft, Corrected By Pinal Dave.

DESCRIPTION
The script was born out of the need to rebuild indexes in a maintenance plan where it would
take too long to do every index in every database, and be too hard to maintain a specific list every time another
database was added or removed.

There are plenty of examples that do a specific database (i.e. one at a time) but I needed something that
traverses all databases and all indexes and rebuilds only those over a certain fragmentation threshold.

*It rebuilds the indexes instead of reorganising them, as I have found this to be more robust in the event of invalid key values etc. and
I like my maint plans to just complete no matter what. Original script used DBCC DBREINDEX; this is a deprecated feature so
this script uses the more current ALTER INDEX.

**Change the @maxfrag variable near the top to adjust the fragmentation level over which indexes will be rebuilt (default 30).
N.B. if you are wondering why the index depth >0 test is in the final execution code and not before, thus reducing the result set, it's
because INDEXPROPERTY must be called when USEing the database in which the index resides.

***Yes, it uses a cursor. I hate cursors. There is always a set based alternative. However, it will always be a relatively small dataset and
it was in the original Microsoft script, so I've made a rare exception.

*/

-- Specify your Database Name
USE [master] ;
GO

--create global temp table to hold the table name info
--IF EXISTS (SELECT 1 FROM tempdb.sys .tables WHERE [name] like '#tbllist%')
-- DROP TABLE #tbllist;

CREATE TABLE #tbllist(FullName varchar (255) NOT NULL, DatabaseName varchar(255), TableName varchar (255) NOT NULL)
GO


--get all three part table names from all databases
sp_msforeachdb
'INSERT INTO #tbllist SELECT ''['' + "?" + ''].['' + [TABLE_SCHEMA] + ''].['' + [TABLE_NAME] + '']'' as FullName, ''['' + "?" + '']'' as DatabaseName, [TABLE_NAME] as TableName
FROM [?].INFORMATION_SCHEMA.TABLES
WHERE TABLE_CATALOG <> ''tempdb'' AND TABLE_TYPE = ''BASE TABLE'''

-- Declare variables
SET NOCOUNT ON ;
DECLARE @fullname VARCHAR (255);
DECLARE @DatabaseName varchar (255);
DECLARE @tableName varchar (255);
DECLARE @execstr VARCHAR (255);
DECLARE @objectid INT ;
DECLARE @indexid INT ;
DECLARE @frag decimal ;
DECLARE @maxfrag decimal ;

-- Decide on the maximum fragmentation to allow for.
SET @maxfrag = 30.0 ;


-- Declare a cursor.
DECLARE tables CURSOR FOR
SELECT FullName, DatabaseName, TableName
FROM #tbllist

-- Create the table.
CREATE TABLE #fraglist (
ObjectName varchar (255),
ObjectId INT ,
IndexName varchar (255),
IndexId INT ,
Lvl INT ,
CountPages INT ,
CountRows INT ,
MinRecSize INT ,
MaxRecSize INT ,
AvgRecSize INT ,
ForRecCount INT ,
Extents INT ,
ExtentSwitches INT ,
AvgFreeBytes INT ,
AvgPageDensity INT ,
ScanDensity decimal ,
BestCount INT ,
ActualCount INT ,
LogicalFrag decimal ,
ExtentFrag decimal );
-- Open the cursor.
OPEN tables;
-- Loop through all the tables in the database.
FETCH NEXT
FROM tables
INTO @fullname, @DatabaseName, @tableName;
WHILE @@FETCH_STATUS = 0
BEGIN;

-- Do the showcontig of all indexes of the table
INSERT INTO #fraglist
EXEC ('DBCC SHOWCONTIG (''' + @fullname + ''')
WITH FAST, TABLERESULTS, ALL_INDEXES, NO_INFOMSGS' );
--put the full table name into the object name, as we need it below
UPDATE #fraglist
SET ObjectName = @fullname
WHERE ObjectName = @tableName

FETCH NEXT
FROM tables
INTO @fullname, @DatabaseName, @tableName;
END;

-- Close and deallocate the cursor.
CLOSE tables ;
DEALLOCATE tables ;

SELECT DISTINCT
IDENTITY(int ,1, 1) as ord
, 'Executing USE ' + T.DatabaseName + '; ' +
'IF ((SELECT INDEXPROPERTY (' + CAST( F.ObjectId as varchar(255 )) + ', ' + CHAR(39 ) + F. IndexName + CHAR( 39) + ', ''IndexDepth'')) > 0) ' +
'ALTER INDEX ' + RTRIM(F.IndexName) + ' ON ' + RTRIM (T. FullName) + ' REBUILD - ' + CAST(LogicalFrag as varchar(5)) + '% Fragmented' as [task_descr]
, 'USE ' + T.DatabaseName + '; ' +
'IF ((SELECT INDEXPROPERTY (' + CAST( F.ObjectId as varchar(255 )) + ', ' + CHAR(39 ) + F. IndexName + CHAR( 39) + ', ''IndexDepth'')) > 0) ' +
'ALTER INDEX [' + RTRIM(F.IndexName) + '] ON ' + RTRIM (T. FullName) + ' REBUILD' as [exec_sql]
INTO #tmp_exec_rebuild_index
FROM #fraglist as F
INNER JOIN #tbllist as T ON T.FullName = f.ObjectName
WHERE LogicalFrag >= @maxfrag
ORDER BY 1


DECLARE @max_loop int,
@loopcount int ,
@exec_sql varchar (4000),
@exec_descr varchar (4000)



SET @max_loop = (SELECT MAX([ord] ) FROM #tmp_exec_rebuild_index)
SET @loopcount = 1

WHILE (@loopcount <=@max_loop )
BEGIN
SET @exec_descr = (SELECT [task_descr] FROM #tmp_exec_rebuild_index WHERE [ord] = @loopcount)
SET @exec_sql = (SELECT [exec_sql] FROM #tmp_exec_rebuild_index WHERE [ord] = @loopcount )
PRINT @exec_descr
EXEC(@exec_sql );
SET @loopcount = @loopcount + 1
END


-- Delete the temporary table.
DROP TABLE #fraglist ;
DROP TABLE #tbllist ;
DROP TABLE #tmp_exec_rebuild_index
GO

[gundugadu]

[quote name='deals2buy' timestamp='1352300019' post='1302757419']

[b]Answer: [/b]Micrisoft released a trace flag which will change the fixed rate of the 20% threshold for update statistics into a dynamic percentage rate
[b]Explanation: [/b]In order to activate this new logic, you need to enable traceflag 2371. As a result the threshold to trigger update statistics will be calculated based on the number of rows in the table. However, the table still needs to have a minimum of 500 rows as the minimum row threshold did not change. As before the update of the statistics will apply the default sample rate which is dynamic and based on the # of rows.


nee yavvva okkadu kuda ee thread chudatam leda?
[/quote]
nenu vunna baaaa
nenu chaduvuthunna

[chelsea]

[url="http://www.youtube.com/watch?v=R5RJlgQTI38&feature=related"]http://www.youtube.com/watch?v=R5RJlgQTI38&feature=related[/url]

index structure

[deals2buy]

[quote name='gundugadu' timestamp='1352392806' post='1302763674']
nenu vunna baaaa
nenu chaduvuthunna
[/quote]
gunduu...ela unnav mama?
[quote name='chelsea' timestamp='1352393155' post='1302763717']
[media=]http://www.youtube.com/watch?v=R5RJlgQTI38&feature=related[/media]

index structure
[/quote]deni gurinchi? no videos in apice

[chelsea]

[quote name='deals2buy' timestamp='1352394714' post='1302763874']
gunduu...ela unnav mama?
deni gurinchi? no videos in apice
[/quote]


index meeda ba dani structure ela vuntadi ani chinna example

[deals2buy]

[quote name='chelsea' timestamp='1352394806' post='1302763888']


index meeda ba dani structure ela vuntadi ani chinna example
[/quote]
ok ghar ki poyinaka dektha

[gundugadu]

[quote name='deals2buy' timestamp='1352394714' post='1302763874']
gunduu...ela unnav mama?

[/quote]

good bane vunna

thu kise re, DB lo janalki SQL bane nerputhunnav ga
good going keep
it up