Simon's SQL

SQL,DBA,tuning,Trouble Shooting,Performance

Introduce Desired State Configuration in Windows Server 2012

Posted by Simon Cho on 2014/08/08

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2013/MDC-B302

Posted in Common | Tagged: , | Leave a Comment »

Basic powershell function call

Posted by Simon Cho on 2014/08/08

http://stackoverflow.com/tags/powershell/info

 

Common Gotchas

Executing EXEs via a path with spaces requires quoting the path and the use of the call operator – &

C:\PS> & 'C:\Program Files\Windows NT\Accessories\wordpad.exe'

Calling PowerShell functions does not require parenthesis or comma separated arguments. PowerShell functions should be called just like a cmdlet. The following examples demonstrates the problem caused by this issue e.g.:

C:\PS> function Greet($fname, $lname) {"My name is '$lname', '$fname' '$lname'"}
C:\PS> Greet('James','Bond') # Wrong way to invoke this function!!
My name is '', 'James Bond' ''

Note that both ‘James’ and ‘Bond’ are packaged up as a single argument (an array) that is passed to the first parameter. The correct invocation is:

C:\PS> Greet James Bond
My name is 'Bond', 'James' 'Bond'

Note that in PowerShell 2.0, the use of Set-StrictMode -version 2.0 will catch this type of problem.

Posted in Common | Tagged: , | Leave a Comment »

DBCC help(‘?’) want to see all hidden DBCC command

Posted by Simon Cho on 2014/07/22

SQL 2005

DBCC HELP(‘?’)
DBCC TraceOn(2580)
DBCC HELP(‘?’)

SQL 2008 and above

DBCC HELP(‘?’)
DBCC TraceOn(2588)
DBCC HELP(‘?’)

 

Posted in Common | Tagged: , , , | Leave a Comment »

DBCC SHRINKFILE isn’t working even simple recovery mode.

Posted by Simon Cho on 2014/07/15

Log file Shrink has several dependency.

1. Log backup(or Checkpoint in simple recovery mode)

2. Replication

3. Mirroring

4. VLF

#1,2,3 can be check by this script

Declare @DBName varchar(255) = ‘test’
select log_reuse_wait_desc
from sys.databases where name= @DBName

So, most case, it’s ok.
But, only #4 can’t be checked by above script.

Please use this file to be able to check “StartOffset” for second VLF since LDF required minimum 2 VLFs.

DBCC LOGINFO

If it’s too big, need to refresh VLF.

big_Offset_VLF

 

 

 

 

But, unfortunately, refresh VLF isn’t simple.

http://www.sqlskills.com/blogs/kimberly/transaction-log-vlfs-too-many-or-too-few/

http://technet.microsoft.com/en-us/library/aa933049%28v=sql.80%29.aspx

http://www.brentozar.com/blitz/high-virtual-log-file-vlf-count/

http://sqlstudies.com/2013/08/26/the-effect-of-vlf-size-on-shrinking-the-log/

Ref. Log_reuse_wait (http://msdn.microsoft.com/en-us/library/ms178534.aspx)
0 = Nothing
1 = Checkpoint
2 = Log backup
3 = Active backup or restore
4 = Active transaction
5 = Database mirroring
6 = Replication
7 = Database snapshot creation
8 = Log Scan
9 = An AlwaysOn Availability Groups secondary replica is applying transaction log records of this database to a corresponding secondary database.
10 = For internal use only
11 = For internal use only
12 = For internal use only
13 = Oldest page
14 = Other (transient)

Posted in Common | Tagged: , , , | Leave a Comment »

SQL Agent Job server.(Centralized job server)

Posted by Simon Cho on 2014/07/03

http://www.informit.com/guides/content.aspx?g=sqlserver&seqNum=315

http://msdn.microsoft.com/en-us/library/ms180992(v=sql.120).aspx

 

Automated Administration Across an Enterprise

 

Automating administration across multiple instances of SQL Server is called multiserver administration. Use multiserver administration to do the following:

  • Manage two or more servers.
  • Schedule information flows between enterprise servers for data warehousing.

 

To take advantage of multiserver administration, you must have at least one master server and at least one target server. A master server distributes jobs to, and receives events from, target servers. A master server also stores the central copy of job definitions for jobs that are run on target servers. Target servers connect periodically to the master server to update their schedule of jobs. If a new job exists on the master server, the target server downloads the job. After the target server completes the job, it reconnects to the master server and reports the status of the job.

The following illustration shows the relationship between master and target servers:

Multiserver administration configuration

If you administer departmental servers across a large corporation, you can define the following:

  • One backup job with job steps.
  • Operators to notify in case of backup failure.
  • An execution schedule for the backup job.

 

Posted in Common | Tagged: , , , , | Leave a Comment »

Drop certain query plan

Posted by Simon Cho on 2014/07/02

DECLARE @ObjName varchar(255)
SET @ObjName = ‘abc’
SELECT c.usecounts
, c.cacheobjtype
, c.objtype
, c.size_in_bytes
, t.text
, p.query_plan
, DB_NAME(p.dbid) AS DBNAME
, p.encrypted
FROM sys.dm_exec_cached_plans c
CROSS APPLY sys.dm_exec_sql_text(plan_handle) t
CROSS APPLY sys.dm_exec_query_plan (plan_handle) p
WHERE usecounts > 1 and t.text like ‘%’+@ObjName+’%’
AND t.text not like ‘%sys.dm_exec_cached_plans%’
GO

–DBCC FREEPROCCACHE (plan_handle);
–ex) DBCC FREEPROCCACHE (0x05000C00F01C44754021DDDA040000000000000000000000);
GO

Posted in Common | Tagged: , , | Leave a Comment »

Alert email sending

Posted by Simon Cho on 2014/06/30

Email send when @objName has data. @objName should include DB name as well.
Ex) @objName = ‘DBA.dbo.vw_Job_Status’

CREATE PROCEDURE [dbo].[dmp_send_email_HTML_format]
@objName VARCHAR(255)
, @Subject VARCHAR(255)
, @PreDescription varchar(8000) = NULL
, @Recipients VARCHAR(8000)
, @importance VARCHAR(255) = ‘High’
, @profile VARCHAR(255) = ‘Public_Profile’ –eMail profile name
as
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*
Return value @r
10001 : object is wrong
10002 : column is wrong
0 : Done or Nodata.
*/

DECLARE @tableHTML NVARCHAR(max)
, @HTML_TH NVARCHAR(max)
, @HTML_TD NVARCHAR(max)
, @sql NVARCHAR(4000)
, @param NVARCHAR(4000)
, @DBName VARCHAR(255)
, @rowcnt INT
, @r INT

DECLARE @tbl_datacheck TABLE (i INT)

SET @DBName = PARSENAME(@objName,3)

SET @r = -1

IF OBJECT_ID(@objName) IS NULL BEGIN
SET @r = 10001
GOTO ERROR;
END

IF @DBName = ” OR @DBName IS NULL BEGIN
SET @DBName = DB_NAME()
END
SET @sql = ‘select top 1 1 from ‘+ @objName

INSERT INTO @tbl_datacheck
EXEC (@sql)
SET @rowcnt = @@ROWCOUNT

IF @rowcnt <=0 BEGIN
SET @r = 0
GOTO ERROR;
END

IF OBJECT_ID('tempdb.dbo.#tbl_column') IS NOT NULL
DROP TABLE #tbl_column
CREATE TABLE #tbl_column (idx INT IDENTITY(1,1), NAME VARCHAR(255))

set @sql = '
INSERT #tbl_column
SELECT name
FROM '+@DBName+'.SYS.syscolumns
WHERE ID = OBJECT_ID(@objName)
ORDER BY colorder
'
set @param = ' @objName varchar(255) '
exec sp_executesql @sql, @param, @objName

SET @rowcnt = @@ROWCOUNT
–PRINT @rowcnt

IF @rowcnt = 0 BEGIN
SET @r = 10002
GOTO ERROR;
END

SELECT @HTML_TH =
CAST ((SELECT th = isnull(NAME,' ')
FROM #tbl_column
FOR XML PATH(''), TYPE
) AS NVARCHAR(MAX) )

–print @HTML_TH
SET @HTML_TD = ''
SELECT @HTML_TD = @HTML_TD + 'td = isnull('+NAME+','' ''), '''', '
FROM #tbl_column

SET @HTML_TD = SUBSTRING(@HTML_TD, 1, LEN(@HTML_TD)-1)

SET @sql = 'SELECT @HTML_TD = CAST ((SELECT '+ REPLACE(@HTML_TD,'','''') +'
FROM '+@objName+'
FOR XML PATH(''tr''), TYPE
) AS NVARCHAR(MAX) )
'

–PRINT @sql

EXEC sp_executesql @sql, N' @HTML_TD NVARCHAR(MAX) OUTPUT ', @HTML_TD OUTPUT

–PRINT @HTML_TD

set @Recipients = isnull(@Recipients,'NowcomDBDevTeam@nowcom.com')

set @subject = @@servername +' – ' + convert(varchar(255), getdate(), 101) + ' ' + convert(varchar(5), getdate(), 108)+' ' + @Subject

SET @tableHTML = '

h1{
font-size:17px;
}
body{
font-size:15px;
}

table{
border-collapse:collapse;
border:1px solid black;
font-size:12px;
}

table th{
font:bold;
border:1px solid black;
padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px;
background-color:lightblue;
font-size:12px;
}

table td{
border:1px solid black;
padding-left:2px;padding-right:2px;padding-top:1px;padding-bottom:1px
font-size:12px;
}

‘+ @subject +’
‘+ ISNULL(@PreDescription,”) + ‘

‘+ @HTML_TH +’


+ @HTML_TD

set @tableHTML = @tableHTML + ‘

SELECT @tableHTML = ‘From Server :’ + ISNULL(@@SERVERNAME, ”) + char(13) + char(10) + ISNULL(@tableHTML, ”);

EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile
, @body_format = ‘HTML’
, @recipients = @Recipients
, @importance = @importance
, @copy_recipients =”
, @subject = @subject
, @body = @tableHTML
IF @@error 0 BEGIN
RAISERROR(‘Error in dmp_send_email_HTML_format. Failed to send Database Mail.’, 16, 1);
END
RETURN 0
ERROR:
return @r
END
Go

Posted in Common | Tagged: , | Leave a Comment »

Selecting Rows Randomly from a Large Table

Posted by Simon Cho on 2014/06/12

http://msdn.microsoft.com/en-us/library/cc441928.aspx

SELECT * FROM Table1
WHERE (ABS(CAST(
(BINARY_CHECKSUM(*) *
RAND()) as int)) % 100) < 10 — Percent

SELECT * FROM Table1 TABLESAMPLE (1 percent) ORDER BY NEWID()

Posted in Common | Tagged: , , | Leave a Comment »

SQL Server Job Agent – Invoke job sequence.

Posted by Simon Cho on 2014/06/11

1. [SQLAgent - Job invocation engine] : execute it based on active schedule and job at scheduled time.

 

exec sp_executesql N'UPDATE msdb.dbo.sysjobactivity SET run_requested_date = DATEADD(ms, -DATEPART(ms, GetDate()),  GetDate()), run_requested_source = CONVERT(sysname, @P1), queued_date = NULL, start_execution_date = NULL, last_executed_step_id = NULL, last_executed_step_date = NULL, stop_execution_date = NULL, job_history_id = NULL, next_scheduled_run_date = NULL WHERE job_id = @P2 and session_id = @P3',N'@P1 int,@P2 uniqueidentifier,@P3 int',1,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',14

 

2. [SQLAgent - Job Manager] : Update start time. Check Permission. Get Jobstep. And execute each step

exec sp_executesql N'DECLARE @startExecutionDate DATETIME SET @startExecutionDate = msdb.dbo.agent_datetime(@P1, @P2) UPDATE msdb.dbo.sysjobactivity SET start_execution_date = @startExecutionDate WHERE job_id = @P3 and session_id = @P4',N'@P1 int,@P2 int,@P3 uniqueidentifier,@P4 int',20140611,144430,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',14

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_has_server_access @login_name = @P1',N'@P1 nvarchar(128)',N'sa'

exec sp_executesql N'EXECUTE msdb.dbo.sp_help_jobstep @job_id = @P1',N'@P1 uniqueidentifier','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

EXECUTE @retval = sp_verify_job_identifiers '@job_name',
                                              '@job_id',
                                               @job_name OUTPUT,
                                               @job_id   OUTPUT,
                                              'NO_TEST'

--Execute each step

3. [SQLAgent - TSQL JobStep(Job 0x...) or SSIS JobStep(Job 0x...)] : Execute each step.

4. [SQLAgent - Job Manager] : Update job history and status.

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status = @P5, @run_date = @P6, @run_time = @P7, @run_duration = @P8, @operator_id_emailed = @P9, @operator_id_netsent = @P10, @operator_id_paged = @P11, @retries_attempted = @P12, @session_id = @P13, @message = @P14',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000)','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',1,0,0,1,20140611,144430,0,0,0,0,0,14,N'Executed as user: NT AUTHORITY\NETWORK SERVICE. The step succeeded.'

5. [SQLAgent - Update job activity] – Update next schedule.

exec sp_executesql N'DECLARE @nextScheduledRunDate DATETIME SET @nextScheduledRunDate = msdb.dbo.agent_datetime(@P1, @P2) UPDATE msdb.dbo.sysjobactivity SET next_scheduled_run_date = @nextScheduledRunDate WHERE session_id = @P3 AND job_id = @P4',N'@P1 int,@P2 int,@P3 int,@P4 uniqueidentifier',20140611,144440,14,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

6. [SQLAgent - Job Manager] – Update last run time

exec sp_executesql N'UPDATE msdb.dbo.sysjobservers SET last_run_date = @P1, last_run_time = @P2, last_run_outcome = @P3, last_outcome_message = @P4, last_run_duration = @P5 WHERE (job_id = @P6) AND (server_id = 0)',N'@P1 int,@P2 int,@P3 int,@P4 nvarchar(4000),@P5 int,@P6 uniqueidentifier',20140611,144430,1,N'The job succeeded.  The Job was invoked by Schedule 30 (test).  The last step to run was step 1 (step01).',0,'676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB'

exec sp_executesql N'EXECUTE msdb.dbo.sp_sqlagent_log_jobhistory @job_id = @P1, @step_id = @P2, @sql_message_id = @P3, @sql_severity = @P4, @run_status = @P5, @run_date = @P6, @run_time = @P7, @run_duration = @P8, @operator_id_emailed = @P9, @operator_id_netsent = @P10, @operator_id_paged = @P11, @retries_attempted = @P12, @session_id = @P13, @message = @P14',N'@P1 uniqueidentifier,@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 int,@P9 int,@P10 int,@P11 int,@P12 int,@P13 int,@P14 nvarchar(4000)','676BBCD8-CE4A-4ADA-8215-A4E9276D6BBB',0,0,0,1,20140611,144430,0,0,0,0,0,14,N'The job succeeded.  The Job was invoked by Schedule 30 (test).  The last step to run was step 1 (step01).'

Done.

Posted in Common | Tagged: , , | Leave a Comment »

OpenXML parentID

Posted by Simon Cho on 2014/05/19

I used long time ago.

To find the it again, I spent a lot of time.

This is really helpful to identify XML parentID and currentID.

OpenXML support it.

 

Metaproperty attribute Description
@mp:id Provides system-generated, document-wide identifier of the DOM node (element, attribute, and so on). This ID is guaranteed to refer to the same XML node as long as the document is not reparsed.An XML ID of 0 indicates that the element is a root element. Its parent XML ID is NULL.
@mp:localname Stores the local part of the name of the node. It is used with prefix and namespace URI (Uniform Resource Identifier) to name element or attribute nodes.
@mp:namespaceuri Provides the namespace URI of the current element. If the value of this attribute is NULL, no namespace is present
@mp:prefix Stores the namespace prefix of the current element name.If no prefix is present (NULL) and a URI is given, indicates that the specified namespace is the default namespace. If no URI is given, no namespace is attached.
@mp:prev Stores the previous sibling relative to a node, thereby, providing information about the ordering of elements in the document.@mp:prev contains the XML ID of the previous sibling that has the same parent element. If an element is at the beginning of the sibling list, @mp:prev is NULL.
@mp:xmltext This metaproperty is used for processing purposes. Is the textual serialization of the element and its attributes and subelements as used in the overflow handling of OPENXML.

 

Parent metaproperty attribute Description
@mp:parentid Corresponds to ../@mp:id
@mp:parentlocalname Corresponds to ../@mp:localname
@mp:parentnamespacerui Corresponds to ../@mp:namespaceuri
@mp:parentprefix Corresponds to ../@mp:prefix

 

http://technet.microsoft.com/en-us/library/aa226531%28v=sql.80%29.aspx

http://technet.microsoft.com/en-us/library/ms178088.aspx

 

DECLARE @X XML, @h INT
SET @X = '<root><element>test</element><element>test2</element></root>'

EXEC sp_xml_preparedocument @h OUTPUT, @x

select @h

SELECT * 
  FROM OPENXML (@h, './root/element',8)
  WITH 
  (
  id bigint '@mp:id'
  , parentid bigint '@mp:parentid'
  , element varchar(255) '.'
  )

 EXEC sp_xml_removedocument @h

 

Posted in Common | Tagged: , , | 1 Comment »

Token-based server access validation failed with an infrastructure error, Error: 18456

Posted by Simon Cho on 2014/04/02

Login failed for user <Server name>\User1′. Reason: Token-based server access validation failed with an infrastructure error. Check

http://blogs.msdn.com/b/sqlserverfaq/archive/2010/10/27/troubleshooting-specific-login-failed-error-messages.aspx

 

Posted in Common | Leave a Comment »

sp_rename will not change sys.syscomments table definition.

Posted by Simon Cho on 2013/08/05


exec sp_rename 'usp_test','uspp_test'
select text from sys.syscomments
where id=object_id('uspp_test')

The text column is still showing as “create procedure usp_test”.

It breaks to run “sp_refreshsqlmodule”

Invalid object name ‘dbo.uspp_test’.

To be able to resolve it, run again “alter procedure ” statement.

Posted in Common | Leave a Comment »

Excel import using XML and T-SQL

Posted by Simon Cho on 2013/05/08

http://www.sqlservercentral.com/articles/ETL/69339/

Posted in Common | Tagged: , | Leave a Comment »

TempDB Misconceptions around TF 1118

Posted by Simon Cho on 2013/03/12

http://www.sqlskills.com/blogs/paul/misconceptions-around-tf-1118/

[Edit 2012:] 4a) What is Paul’s recommendation for using trace flag 1118? Everyone should turn it on, on all instances of SQL Server, from SQL Server 2000 onwards. There’s no down-side to having it turned on.

http://www.sqlskills.com/blogs/paul/a-sql-server-dba-myth-a-day-1230-tempdb-should-always-have-one-data-file-per-processor-core/

But there’s now even better guidance, and what I also recommend. At PASS in 2011, my good friend Bob Ward, who’s the top guy in SQL Product Support, espoused a new formula: if you have less than 8 cores, use #files = #cores. If you have more than 8 cores, use 8 files and if you’re seeing in-memory contention, add 4 more files at a time.

http://technet.microsoft.com/library/Cc966545

Use TF-1118. Under this trace flag SQL Server allocates full extents to each tempdb object, thereby eliminating the contention on SGAM page. This is done at the expense of some waste of disk space in tempdb. This trace flag has been available since SQL Server 2000. With improvements in tempdb object caching in SQL Server 2005, there should be significantly less contention in allocation structures. If you see contention in SGAM pages, you may want to use this trace flag. Cached tempdb objects may not always be available. For example, cached tempdb objects are destroyed when the query plan with which they are associated is recompiled or removed from the procedure cache.

Posted in Common | Tagged: , , | Leave a Comment »

Bulk load data conversion error (overflow) SQLINT (BCP)

Posted by Simon Cho on 2013/03/07

http://msdn.microsoft.com/en-us/library/ms189110%28v=sql.90%29.aspx

Only SQLCHAR is allowed for fmt files if the file storage type is Char.

 

 

1 Data files that are stored in character format use char as the file storage type. Therefore, for character data files, SQLCHAR is the only data type that appears in a format file.

2 You cannot bulk import data into text, ntext, and image columns that have DEFAULT values.

When you bulk export data from an instance of SQL Server to a data file:

  • You can always specify char as the file storage type.
  • If you enter a file storage type that represents an invalid implicit conversion, bcp fails; for example, though you can specify int for smallint data, if you specify smallint for int data, overflow errors result.
  • When noncharacter data types such as float, money, datetime, or int are stored as their database types, the data is written to the data file in the SQL Server native format.

Posted in Common | Tagged: , , | Leave a Comment »

Paging file for SQL server

Posted by Simon Cho on 2013/02/27

Please don’t put 1.5 time of Physical memory.

Less amount is better for SQL server if you have a x64 machine and enough physical memory.

http://support.microsoft.com/kb/889654/en-us

http://blogs.msdn.com/b/buckwoody/archive/2010/06/29/the-windows-page-file-and-sql-server.aspx

http://www.brentozar.com/archive/2009/11/christian-bolton-on-sql-server-memory/

http://blogs.technet.com/b/askperf/archive/2007/12/14/what-is-the-page-file-for-anyway.aspx

http://www.brentozar.com/archive/2008/03/sql-server-2005-setup-checklist-part-1-before-the-install/

 

Posted in Common | Tagged: , | Leave a Comment »

BACKUP DATABASE TO DISK = N‘NUL’

Posted by Simon Cho on 2013/01/04

http://sqlservermct.wordpress.com/2011/10/07/backup-database-to-disk-n%E2%80%98nul%E2%80%99-%E2%80%93-and-misconceptions/

Veeam can truncate log base on some option.

http://www.veeam.com/vmware-backup/help-center/v6_1/hyperv/index.html?vmware_replica_guest_processing.htm

It make backup chain broken.
When they perform truncate log, you can see 2 of the log like this.

SQL VSS is normal behavior to create SnapShot on disk.
But, Veeam Backup and replication with truncate log isn’t good option to manage database.

I recommend that do not enable truncate log on Veeam replication manager.

select top 10 * 
  from msdb.dbo.backupset
 where is_snapshot=1

--It's due to SQL VSS.
--Program_Name : "Microsoft SQL Server VSS Writer"
--ex)
--BACKUP DATABASE [DBNAME] TO VIRTUAL_DEVICE='{4C5C20F9-FE32-4FCA-BE43-FFFFFFFFFFFF}1' WITH SNAPSHOT,BUFFERCOUNT=1,BLOCKSIZE=1024

select top 10 * 
  from msdb.dbo.backupmediafamily
 where physical_device_name='NUL'

--It's due to Veeam.
--Program_Name : "Veeam Backup and Replication"
--ex)
--BACKUP LOG [DBNAME] TO DISK = 'NUL'

Posted in Common | Tagged: , , , | Leave a Comment »

uniqueidentifier : GUID in SQL

Posted by Simon Cho on 2013/01/02

1. newsequentialid()

http://msdn.microsoft.com/en-us/library/ms189786%28v=sql.100%29.aspx

This is one of the function to generate sequence GUID to reduce fragmentation.

But, it’s not grantee uniqueness.
It’s wrapped by windows function UuidCreateSequential, http://msdn.microsoft.com/en-us/library/aa379322%28VS.85%29.aspx.

Creates a GUID that is greater than any GUID previously generated by this function on a specified computer since Windows was started.
After restarting Windows, the GUID can start again from a lower range, but is still globally unique.
When a GUID column is used as a row identifier, using NEWSEQUENTIALID can be faster than using the NEWID function.
This is because the NEWID function causes random activity and uses fewer cached data pages. Using NEWSEQUENTIALID also helps to completely fill the data and index pages.

2. uniqueidentifier sorting.

http://sqlblog.com/blogs/alberto_ferrari/archive/2007/08/31/how-are-guids-sorted-by-sql-server.aspx

3. uniqueidentifier converting

SELECT CONVERT(uniqueidentifier, 0x000102030405060708090A0B0C0D0E0F)
UNION ALL
SELECT CONVERT(uniqueidentifier, 0x00000000000000000123456789ABCDEF)
UNION ALL
SELECT CONVERT(uniqueidentifier, 0x000000000123456789ABCDEF00000000)
UNION ALL
SELECT CONVERT(UNIQUEIDENTIFIER, '00000000-0123-4567-89AB-CDEF00000000')

Posted in Common | Tagged: , , | Leave a Comment »

Contact information

Posted by Simon Cho on 2012/12/20

simon@simonsql.com

Tuning, Consulting and all DB related questions.

Posted in Contact us | Leave a Comment »

Cross-Database Access

Posted by Simon Cho on 2012/12/05

http://www.sommarskog.se/grantperm.html

 

Posted in Common | Tagged: , , | Leave a Comment »

 
Follow

Get every new post delivered to your Inbox.