Blog – Fabiano Neves Amorim

Hi folks, a question came to me asking about if is necessary update the statistics even after an index rebuild. Because the answer is it depends and we don’t like this answer I did some tests to show you when you need update statistics after a rebuild and when not.

As we know SQL Server could create a statistic if the Auto Update Statistics parameter is Enabled. These statistics are that one starting with “_WA_Sys…”, be aware that SQL Server do not auto-create multi-column statistics automatically(except with indexes). We also have statistics creates by Indexes, and statistics created manually with the CREATE STATISTICS command.

So, let’s check all of this… First I’ll create a table with four columns, and populate with some garbage data.

use tempdb

GO

IF OBJECT_ID(‘T1’) IS NOT NULL

DROP TABLE T1

GO

CREATE TABLE T1 (Col1 Int, Col2 Int, Col3 Int, Col4 Int)

GO

INSERT INTO T1 VALUES(1,1,1,1)

GO 1000

CREATE CLUSTERED INDEX ix_Col1 ON T1(Col1)

CREATE INDEX ix_Col2 ON T1(Col2)

CREATE STATISTICS stats_Col3 ON T1(Col1)

GO

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

As you can see we have 3 statistics for the table T1. Two for the index and one called stats_Col3. Now in order to create an auto-statistics I’ll run the following query using the column C4 into the where condition.

SELECT * FROM T1

WHERE Col4 = 1

GO

If we check the sysindexes again, now we’ll see the auto-created statistic.

Following let’s check some different scenarios to rebuild an index.

Scenario 1

Now, the following script runs a DBCC REINDEX to rebuild the Clustered Index ix_Col1.

— Scenario 1: DBCC REINDEX in the Clustered Index

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

DBCC DBREINDEX(T1, ix_Col1)

GO

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

As we can see in the picture above, the statistics for the indexes were updated, but the auto-created and the manually don’t.

Scenario 2

— Scenario 2: DBCC REINDEX in the NonClustered Index

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

DBCC DBREINDEX(T1, ix_Col2)

GO

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

As expected now the only statistic updated was the ix_Col2.

Scenario 3

— Scenario 3: DBCC REINDEX without specify the index. That means all index must be updated.

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

DBCC DBREINDEX(T1)

GO

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

Now all statistics were updated, including the auto and manually created.

Scenario 4

— Scenario 4: ALTER INDEX to update the Clustered Index

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

ALTER INDEX ix_Col1 ON T1 REBUILD;

GO

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

Here, look that just to use the ALTER INDEX instead the DBCC REINDEX things are different. In the Scenario 1 we got the two index Updated, and using the ALTER INDEX we don’t, just the ix_Col1 was updated.

Scenario 5

— Scenario 5: ALTER INDEX to update the NonClustered Index

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

ALTER INDEX ix_Col2 ON T1 REBUILD;

GO

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

As expected now the only statistic updated was the ix_Col2. This is the same behavior that the Scenario 2.

Scenario 6

— Scenario 6: ALTER INDEX to update the ALL Index

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

ALTER INDEX ALL ON T1 REBUILD;

GO

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

Now both index statistics were update, but the auto and manually created don’t. Note that we expect that this act like the Scenario 3 where we use the DBCC REINDEX, but this is don’t have the same behavior.

It depends

As you can see in the scenarios above it depends to the command you are using may some statistics are updated and some not. But that’s is not all J, it also depends the SQL Server version you are using. I ran the scripts above into SQL Server 2008 R2 and SQL Server 2008 and the results were the same, but when I ran into SQL Server 2005 SP3 I got a slight difference.

Following we can see that the Scenario 1 don’t update all index statistics, just the clustered index.

— Scenario 1: DBCC REINDEX in the Clustered Index

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

DBCC DBREINDEX(T1, ix_Col1)

GO

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

The big surprise was when I run it into SQL Server 2000 SP4. The Scenario 1 acts very different to the other versions.

— Scenario 1: DBCC REINDEX in the Clustered Index

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

DBCC DBREINDEX(T1, ix_Col1)

GO

SELECT name, Stats_date(id, indid) AS LastUpdate

FROM sysindexes

WHERE id = Object_id(‘T1’)

GO

As you can see, when you update the Clustered Index all statistics are updated, including the auto and manually created.

Resume

Following are a resume of the scenarios.

Pessoal, é com GRANDE prazer que falo a vocês que estamos trazendo os cursos de T-SQL da SolidQ para o Brasil.

Eu estou trabalhando na tradução do material de 2 cursos criados pelo nosso amigo e gênio Itzik Ben-Gan, vai por mim, tem muita coisa boa, e informações privilegiadas que você só verá no curso… vale muito a pena.

Portanto se você é DBA ou Desenvolvedor, não perca a chance…. logo estarei publicando mais informações sobre isso, fique de olho.

É claro que o treinamento no Brasil terá alguns “toques” especiais que irei incluir no curso , com minha experiência de como usamos o T-SQL no Brasil mais o material da SolidQ, não tenho dúvida que iremos ministrar o melhor curso de T-SQL da América Latina.

Por enquanto para aguçar sua vontade, segue uma breve descrição dos dois cursos de T-SQL.

Fundamentos do T-SQL (Não se deixe enganar pelo nome, é um curso com muitos exemplos nível 400)

• Os tópicos cobertos por esse curso incluem: Background para Consultas e Programação em T-SQL; Consultas em uma única tabela; Joins; Sub-Consultas; Table Expressions; Operações com Conjuntos; Pivot, Unpivot e Groupins Sets; Modificação de Dados; Transações; Overview dos Objetos de Programação.
• Para complementar a experiência de aprendizado os alunos receberão exercícios que irão habilitá-los de praticar o que eles aprenderam.

T-SQL Querying Avançado, Programação e Tuning para SQL Server 2005 e 2008 (Nivel 400)

• Este curso é direcionado em escrever e aperfeiçoar consultas e programação com T-SQL no SQL Server 2005 e 2008. Neste curso você irá aprender os detalhes e capacidades do T-SQL nas seguintes áreas: Logical Query Processing, Query Tunning, SubQueries, Ranking, Functions, Joins e Grouping sets; Aggregating e Pivot de dados; TOP and APPLY; Modificações de dados; Problemas relacionados a Data Types; Objects Programáveis (SQL dinâmico, Views, User Defined Functions, Stored Procedures, Triggers, Transações e Concorrência, Controle de Exceções); Árvores e Hierarquias.

Galera, imperdível… agora deixo outra supresa… Estou preparando um curso e um seminário de Indexação e Execution Plans… logo irei publicar o conteudo do curso.

Agora deixo uma pergunta… você gostaria de fazer estes cursos? Responda aqui no blog com um comentário…

Abraços

I was doing some tests with memory grant,sort warnings and amount of avaliable memory, so here are some pictures of my tests…

When I wrote this is an indication, I don´t mean 100% of certain that if you add more memory you will have a performance gain. But sort warnings can indicate memory pressure.

For instance, suppose a very concurrently scenario, with many queries requiring memory grant. When I run a query the server calculates the memory limit for that query. By default, this is 25% (20% on 32bit SQL 2005) of total query memory (which is set by memory broker as about 90% of server memory). This per-query limit helps to prevent one query from dominating the whole server.

Following is some picture of a simulation of this scenario.

1024 MB of Memory

EXEC sys.sp_configure N’max server memory (MB)’, N’1024′

GO

RECONFIGURE WITH OVERRIDE

GO

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

GO

512 MB of Memory

EXEC sys.sp_configure N’max server memory (MB)’, N’512′

GO

RECONFIGURE WITH OVERRIDE

GO

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

GO

256 MB of Memory

EXEC sys.sp_configure N’max server memory (MB)’, N’256′

GO

RECONFIGURE WITH OVERRIDE

GO

DBCC FREEPROCCACHE

DBCC DROPCLEANBUFFERS

GO

64 MB of Memory

As you can see according to the memory change, the SQL can´t grant memory to some queries. And with just a few memory available the sort is exhibited.

Instead of change the available memory I could add more threads into SQLQueryStress to simulate the concurrency into the server, if you do this, may you see

some Sort Warnings into Profiler.

I’m not saying that you are wrong, I totally agree with you that SQL uses cardinality to estimate the amount of memory to the query, and the main problem is, a wrong estimation can lead to a bad memory grant. The point here is, yes, the sort warning can indicate a memory pressure.

SQLQueryStrees by Adam Machanic http://www.datamanipulation.net

Hoje li uma muito boa…

Lembro uma vez em que meu amigo Felipe Ferreira (Blog|Twitter) disse…

– Não use asterisco em suas consultas, nunca, asterisco é coisa do Demonio! 🙂

Agora te pergunto, como fazer isso sem violar esta regra sagrada? Que tal o código abaixo?

🙂

Abraços

Administrando SQL Server 2008 R2 – Mirroring/Resource Governor e BackupCompression

Galera conforme prometido, segue o link para o PowerPoint e os arquivos utilizados na WebCast de hoje.

http://cid-52eff7477e74caa6.office.live.com/self.aspx/Publica/Administrando%20SQL%20Server%202008%20R2%20%e2%80%93%20Mirroring^J%20Resource%20Governor%20e%20BackupCompression

Abraços

Fellows after some time of hard working and to be very busy as usual, I’m back with the operator of the week articles at simple-talk,

Please take a look,

http://www.simple-talk.com/sql/learn-sql-server/showplan-operator-of-the-week—stream-aggregate/

Please, votes are wellcome 🙂

Thanks

O que você acha que a consulta abaixo irá retornar?

use tempdb
go

if object_id('t1') is not null

drop table t1
create table t1 (Col1 Int)
insert into t1 values(1)
GO

select * from tempdb . . t1
where tempdb . dbo . t1 . col1 = 1

Dica interessante…

Para tristeza de muitos a Microsoft limitou o Backup Compression para apenas versão Enterprise… Acho que depois de muito reclamarem, no SQL Server 2008 R2 a versão Standard já suporta esta feature. 🙂 … Show…

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

Hi, I just created a connect Item about a query that IMHO is a problem with Query Optimizer.

I appreciate if you take a look and vote.

https://connect.microsoft.com/SQLServer/feedback/details/587729/query-optimizer-create-a-bad-plan-when-is-not-null-predicate-is-used

Regards

Quick post…

I put a comment in the Greg’s article on Simple-Talk, http://www.simple-talk.com/sql/database-administration/creative-solutions-by-using-a-number-table/

The solution is, to change that:

To that: