Importance of Statistics and Update of it

How important is to have statistics up to date versus fragmentations.

I start with a fresh copy of WideWorldImporters , call it WideWorldImporters_Test, in case of already existing database.

use master
go

restore database WideWorldImporters_Test
from disk = ‘D:\Kituri\SQL Server\WideWorldImporters-Full.bak’
with
move ‘WWI_Primary’ to ‘D:\Databases\DATA\WideWorldImporters_Test.mdf’,
move ‘WWI_UserData’ to ‘D:\Databases\DATA\WideWorldImporters_Test_UserData.ndf’,
move ‘WWI_Log’ to ‘D:\Databases\DATA\WideWorldImporters_Test.ldf’,
move ‘WWI_InMemory_Data_1’ to ‘D:\Databases\DATA\WideWorldImporters_Test_InMemory_Data_1’,
replace, stats=10

Here is a simple query:

select *
from Sales.Orders
where PickedByPersonID = 1

The execution plan is:

Let’s take a look at fragmentation:

select i.name as idxName
,ips.avg_fragmentation_in_percent
,ips.fragment_count
,ips.avg_page_space_used_in_percent
,ips.page_count
,STATS_DATE(o.object_id, i.index_id) as statsDate
from sys.dm_db_index_physical_stats(db_id(‘WideWorldImporters_test’),OBJECT_ID(‘Sales.Orders’),NULL,NULL,’DETAILED’) as ips
inner join sys.objects as o
ON o.object_id = ips.object_id
inner join sys.indexes as i
ON o.object_id = i.object_id
AND ips.index_id = i.index_id
where
i.name =’FK_Sales_Orders_PickedByPersonID’

Output:

99 precent (external/logical fragmentation) with 53 percent (internal/physical fragmentation)
So, high fragmentation, and the index (FK_Sales_Orders_PickedByPersonID) is not picked.

Now, let’s update the statistic of this index :
update statistics Sales.Orders (FK_Sales_Orders_PickedByPersonID) With fullScan

Looking, again , at the fragmentation :

The index statistics are updated, but the fragmentation is still high.
Key Question: the execution plan is still the same , or …

select *
from Sales.Orders
where PickedByPersonID = 1

Execution plan has changed.


Conclusion:
1 – Have statistics up to date, is the most important thing
2 – Fragmentation is important, but not as statistics.
3 – Rebuilding an index is doing :

• Put in order the pages
• Update statistics with FULL SCAN

So, first do an update statistics with full scan, then run the query. If this doesn’t take you over the cross line, the next step is to rebuild it. And if this doesn’t take you , over the line, maybe you need to rewrite the Query or call someone.

S

Random password

How to generate a six character password.

You have to choose from a list of allowed characters (

(SELECT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’ AS srcText , 62 as srcLen) AS S

)

select
empID as empID
,p.psw as psw1
,ca2.psw1+ ” +ca2.psw2+ ” +ca2.psw3+ ” +ca2.psw4+ ” +ca2.psw5+ ” +ca2.psw6 as psw2
–,len(ca2.psw1+ ” +ca2.psw2+ ” +ca2.psw3+ ” +ca2.psw4+ ” +ca2.psw5+ ” +ca2.psw6) as psw2Len
–,ca1.pozChar1,ca1.pozChar2,ca1.pozChar3,ca1.pozChar4,ca1.pozChar5,ca1.pozChar6
from dbo.Employees
CROSS APPLY
(SELECT ‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789’ AS srcText , 62 as srcLen) AS S
CROSS APPLY
(SELECT CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar1,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar2,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar3,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar4,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar5,
CAST(ROUND( (s.srcLen-1 – 0 ) *RAND(CHECKSUM(NEWID())) + 1,0) as int) as pozChar6) as ca1
CROSS APPLY
(SELECT SUBSTRING(s.srcText,ca1.pozChar1,1) as psw1,
SUBSTRING(s.srcText,ca1.pozChar2,1) as psw2,
SUBSTRING(s.srcText,ca1.pozChar3,1) as psw3,
SUBSTRING(s.srcText,ca1.pozChar4,1) as psw4,
SUBSTRING(s.srcText,ca1.pozChar5,1) as psw5,
SUBSTRING(s.srcText,ca1.pozChar6,1) as psw6) as ca2

CROSS APPLY
(
    SELECT CHAR(ROUND(   (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' + 
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  (122-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) +   '' +
            CHAR(ROUND(  ( 57-49 - 1) *RAND(CHECKSUM(NEWID())) + 49,0)) as psw
)p

Some very interesting script is here : https://stackoverflow.com/a/15038572/3992746

S