SSMS (SQL SERVER MANAGEMENT STUDIO) slow startup – solved

SSMS (SQL SERVER MANAGEMENT STUDIO) slow startup and how to optimize SSMS startup time :
To sump up, everytime you open SSMS it will check some certs, so, do the following if you want faster load times :

Open IE > TOOLS > OPTIONS > ADVANCED > UNCHECK PUBLISHER CERTIFICATE REVOCATION .

Analyzing SQL SERVER 2005 LONG RUNNING QUERIES

I’m leaving this tip for you to find what exactly is running on sql server.

SELECT r.session_id, s.HOST_NAME, s.PROGRAM_NAME,
s.host_process_id, r.status, r.wait_time,
wait_type, r.wait_resource,
SUBSTRING(qt.text,(r.statement_start_offset/2) +1,
(CASE WHEN r.statement_end_offset = -1

THEN LEN(CONVERT(nvarchar(max), qt.text)) * 2

ELSE r.statement_end_offset

END -r.statement_start_offset)/2)

AS stmt_executing,r.blocking_session_id,
r.cpu_time,r.total_elapsed_time,r.reads,r.writes,
r.logical_reads, r.plan_handle

FROM sys.dm_exec_requests r

CROSS APPLY sys.dm_exec_sql_text(sql_handle) as qt, sys.dm_exec_sessions s

WHERE r.session_id > 50 and r.session_id=s.session_id
ORDER BY r.session_id, s.host_name, s.program_name, r.status

MICROSOFT SQL SERVER 2005 Partitioning (English Version)

SQL SERVER 2005 Partitioning <- Can read a fully formated .doc in here .

Microsoft worked well in the Microsoft SQL Server 2005 (even better in MSSQL Server 2008), bringing a shine to their RDBMS engine and building it to be a real Enterprise Class Product.

I’m not going to write about all the new features of the OLTP Engine, I will focus this article on Database Partition, giving you a real case scenario.

I. Benefits and Limitations of Partitioning (HEAPS/CLUSTERED/NONCLUSTERED).

II. Implementation Methodology.  
 
III. Partitioning Management.  

 
I. Benefits and Limitations of Partitioning (HEAPS/CLUSTERED/NONCLUSTERED): 
 
+ 
 
Data can be accessed through multiple partitions in parallel, this will results in faster DML.

Different partitions can be managed separately.  
 
Management of history more efficient (without having to leave the table).  
 
- 
 
Limit of 1000 Partitions by Table.

 
Inability to use Indexed Views.  
 

II. Implementation Methodology.

I’m going to focus in the most common scenario:

Typically, in a DW environment there is a large Fact Table (a.k.a the Monster).

In this article the Fact Table contains all the banking movements of all accounts within the last 4 years in the MAIN database and the remaining years in a HISTORY database.

In this case, because there is a chronology to follow, I will use the extract day date column of the table as the KEY to our Partition Strategy.

In this situation, I recommend the creation of a Partitioned Clustered Index because typically all access to the fact table always has the date in the SARG.  

The fact table will be partitioned by month. 
 

Technical approach:  

Storage Arquitecture – 3 RAID-5 Partition (2TB each), 1 RAID 01 for Transaction Log and 1 more RAID 01 for Non-Clustered indexes.

First off all you will need to develop the Partition Function.

The Partition Function defines the values which the partition scheme will bind with the Filegroups.

Our boundary will be the date, and right range(boundary right aligned).

CREATE PARTITION FUNCTION PFFactTableMonthly (SMALLDATETIME)

AS RANGE RIGHT

FOR VALUES

     (

            ‘2007-01-01′,‘2007-02-01′,‘2007-03-01′,‘2007-04-01′,‘2007-05-01′,

            ‘2007-06-01′,‘2007-07-01′,‘2007-08-01′,‘2007-09-01′,‘2007-10-01′,

            ‘2007-11-01′,‘2007-12-01′,‘2008-01-01′,‘2008-02-01′,‘2008-03-01′,

            ‘2008-04-01′,‘2008-05-01′,‘2008-06-01′,‘2008-07-01′,‘2008-08-01′,

            ‘2008-09-01′,‘2008-10-01′,‘2008-11-01′, ‘2008-12-01′,‘2009-01-01′,

            ‘2009-02-01′

            )

 
Create the Datafiles and assign each file to a differente Filegroup. In this situation 27 Datafiles/Filegroups will be created.

Now that the Filegroups are created, assign them to a Partition Scheme that acts like a ROUTER, because it will forward the data to a specified path.

The Partition Scheme will use the Partition Function to link the logical layer to the physical layer.

CREATE PARTITION SCHEME PSFactTableMonthly

AS PARTITION PFFactTableMonthly

TO (

      FG_FactTable_BASE,FG_FactTable_200701,FG_FactTable_200702,

      FG_FactTable_200703,FG_FactTable_200704,FG_FactTable_200705,

      FG_FactTable_200706,FG_FactTable_200707,FG_FactTable_200708,

      FG_FactTable_200709,FG_FactTable_200710,FG_FactTable_200711,

      FG_FactTable_200712,FG_FactTable_200801,FG_FactTable_200802,

      FG_FactTable_200803,FG_FactTable_200804,FG_FactTable_200805,

      FG_FactTable_200806,FG_FactTable_200807,FG_FactTable_200808,

      FG_FactTable_200809,FG_FactTable_200810,FG_FactTable_200811,

      FG_FactTable_200812,FG_FactTable_200901,FG_FactTable_200902

      )

Now that we have the Partition Scheme, just create the Clustered Index on the Fact table with a FILLFACTOR 90% with PAD_INDEX on.

CREATE CLUSTERED INDEX [IDXC_FACTTABLE] ON MIS.FACTTABLE

      (

      datecolumn

      ) 

      WITH( PAD_INDEX = ON, FILLFACTOR = 90, STATISTICS_NORECOMPUTE = OFF,

      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)  

on PSFactTableMonthly (DATECOLUMN) 

III. Partitioning Management. 

Now, that our table is partitioned, you are wondering, what will happen if there is an insert that has a date >= ‘2009-03-01′, what will happen is that all the data will be placed in the FG_FactTable_200902 s because the data is right aligned as specified when we created the Partition Function.

To maintain the data equally distributed between Filegroups, you must plan the future growth of you Database and continue adding Filegroups to the Partition Scheme and continuing to add dates to the Partition Function.

Add a FG to the Partition Scheme:

ALTER PARTITION PSFactTableMonthly SCHEME

NEXT USED [FG_FactTable_200903].  

Add a new boundary to the Partition Function:

ALTER PARTITION FUNCTION PFFactTableMonthly ()

SPLIT RANGE (‘20090301′)  

Know the detail about the Partition Function :

SELECT * FROM sys.partition_functions

WHERE name = ‘PFFactTableMonthly’ 

Show the boundaries of the Partition Function :

SELECT a.name, b.* FROM sys.partition_functions a, sys.partition_range_values b

WHERE a.function_id = b.function_id

and a.name = ‘PFFactTableMonthly’

Show Allocation Units

SELECT object_name(object_id) AS name,

    partition_id, partition_number AS pnum, rows,

    allocation_unit_id AS au_id, type_desc as page_type_desc,

    total_pages AS pages

FROM sys.partitions p JOIN sys.allocation_units a

   ON p.partition_id = a.container_id

WHERE object_id=object_id(’schema.FactTable) 
 
 
 
 
 

From my point of view this is the best statement there is available to have a neat overview of your partitioned table, because it shows the INDEX_ID, Partition_Number, Filegroup that is associated the respective Partition_Number, ROWS, PAGES and the date that is associated with the FileGroup. 

SELECT OBJECT_NAME(i.object_id) as Object_Name,

            i.index_id AS Index_ID,

         p.partition_number,

•  
  fg.name AS Filegroup_Name,

•  
  rows,

•  
  au.total_pages,

         CASE boundary_value_on_right

             WHEN 1 THEN ‘less than’

•  
        ELSE ‘less than or equal to’ END as ‘comparison’, value

FROM sys.partitions p JOIN sys.indexes i

      ON p.object_id = i.object_id and p.index_id = i.index_id

       JOIN sys.partition_schemes ps

                ON ps.data_space_id = i.data_space_id

       JOIN sys.partition_functions f

                   ON f.function_id = ps.function_id

       LEFT JOIN sys.partition_range_values rv

     ON f.function_id = rv.function_id

                    AND p.partition_number = rv.boundary_id

     JOIN sys.destination_data_spaces dds

             ON dds.partition_scheme_id = ps.data_space_id

                  AND dds.destination_id = p.partition_number

     JOIN sys.filegroups fg

                ON dds.data_space_id = fg.data_space_id

     JOIN (SELECT container_id, sum(total_pages) as total_pages

                     FROM sys.allocation_units

                     GROUP BY container_id) AS au

                ON au.container_id = p.partition_id

     WHERE OBJECT_NAME(i.object_id) = ‘FactTable’ 

Some useful Tips :

• Always use a rule on DataFiles/FileGroup nomenclature, to make it easier to understand the dependencies.
• When partitioning tables, don’t randomize the creation of the DataFiles, for example put the DF1 on StorageA, DF2 on StorageB, DF3 on StorageC, DF4 on StorageA,DF4 on StorageB…
• For best performance align your non-clustered indexes with the partition.
• After the end of the previous month, rebuild the respective previous partition number.
• Be creative !

Paulo Condeça. paulo at citen dot net

MICROSOFT SQL SERVER 2005 Partitioning (Versão Portuguesa)

Objectivo

I. Benefícios e Limitações de Partitioned Clustered INDEX em SQL SERVER 2005.

II. Metodologia de Implementação.

III. Gestão de Particionamento.

Descrição

I. Benefícios e Limitações de Partitioned Clustered INDEX em SQL SERVER 2005:

+

Dados podem ser acedidos através de múltiplas partições em paralelo.

Diferentes partições podem ser geridas em separado.

Gestão de Histórico mais eficiente, sem ter que sair da tabela.

-

Limite de 1000 Partições por Tabela.
Impossibilidade de utilização de Indexed Views.

II. Metodologia de Particionamento TabelaXPTO:

Esta abordagem vai ter como base de trabalho um particionamento trimestral.

1. Criar a função de particionamento com DataType SMALLDATETIME como parâmetro.

CREATE PARTITION FUNCTION PFTabelaXPTOQuarterly (SMALLDATETIME)
AS RANGE RIGHT
FOR VALUES (
‘2006-07-01′,’2006-10-01′,
‘2007-01-01′,’2007-04-01′,’2007-07-01′,
‘2007-10-01′,’2008-01-01′,’2008-04-01′
)

2. Adicionar 10 FILEGROUPS À BD.

ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_QBASE
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q1
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q2
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q3
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q4
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q5
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q6
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q7
ALTER DATABASE DBA ADD FILEGROUP FG_TABELAXPTO_Q8

3. Implementar a Partition Scheme PSTabelaXPTOQuarterly, com a Partition Function PFTabelaXPTOQuarterly como parametro.

CREATE PARTITION SCHEME PSTabelaXPTOQuarterly
AS PARTITION PFTabelaXPTOQuarterly
TO
(
FG_TABELAXPTO_QBASE,
FG_TABELAXPTO_Q1,
FG_TABELAXPTO_Q2,
FG_TABELAXPTO_Q3,
FG_TABELAXPTO_Q4,
FG_TABELAXPTO_Q5,
FG_TABELAXPTO_Q6,
FG_TABELAXPTO_Q7,
FG_TABELAXPTO_Q8

)

• Nesta situação, o primeiro FG tem todos os dados desde Abril 2006 até Julho 2006.
• Os seguintes FG’s tem a data que correspondente ao valor de cada partition.
• O último FG tem todos os dados com data superior a Abril 2008.

4. Implementar o Partitioned Clustered Index na TabelaXPTO na Partition Scheme PSTabelaXPTOQuarterly .

CREATE CLUSTERED INDEX PCIX on dbo.posicao_contrato (data_contrato)
WITH (PAD_INDEX = ON, FILLFACTOR = 90)
ON PSTabelaXPTOQuarterly (data_contrato)

III. Gestão do Particionamento :

Com o exemplo de particionamento actual, surge a questão do que fazer quando se

começar a inserir registos com data >=Julho (2008Q3)?.

• Antes de se chegar ao final do Trimestre, cria-se um novo DataFile e Filegroup ( exemplo: FG_TABELAXPTO_2008Q3 ).

• Altera-se a PartitionScheme, adicionando-lhe um Filegroup.
ALTER PARTITION SCHEME PSTabelaXPTOQuarterly
NEXT USED [FG_TABELAXPTO_2008Q3] .

• Altera-se a PartitionFunction, adicionando-lhe uma nova Boundary(fronteira).
ALTER PARTITION FUNCTION PFTabelaXPTOQuarterly ()
SPLIT RANGE (‘20080701′);

Como saber quantas partições estão associadas ao Clustered Index da TabelaXPTO:

SELECT * FROM sys.partitions
WHERE object_id=object_id(‘posicao_contrato’)

Informação relativa à partition function utilizada.

SELECT * FROM sys.partition_functions
WHERE name=’PFTabelaXPTOQuarterly’

Mostra as boundaries da Partition Function .

SELECT a.name,b.* FROM sys.partition_functions a, sys.partition_range_values b
WHERE a.function_id=b.function_id
and a.name=’PFTabelaXPTOQuarterly’

Informação básica de segmentação de registos por partição

SELECT *
FROM sys.partitions
WHERE object_id=object_id(‘posicao_contrato’)
Query que retorna um registo por partition_number, contem informação de
que Filegroups estão associados ás Partitions, quantos registos, quantas pages e as

respectivas Boundary.

SELECT OBJECT_NAME(i.object_id) AS Object_Name,
i.index_id AS Index_ID,
p.partition_number, fg.name AS Filegroup_Name, rows, au.total_pages,
CASE boundary_value_on_right
WHEN 1 THEN ‘less than’
ELSE ‘less than or equal to’ END as ‘comparison’, value
FROM sys.partitions p JOIN sys.indexes i
ON p.object_id = i.object_id and p.index_id = i.index_id
JOIN sys.partition_schemes ps
ON ps.data_space_id = i.data_space_id
JOIN sys.partition_functions f
ON f.function_id = ps.function_id
LEFT JOIN sys.partition_range_values rv
ON f.function_id = rv.function_id
AND p.partition_number = rv.boundary_id
JOIN sys.destination_data_spaces dds
ON dds.partition_scheme_id = ps.data_space_id
AND dds.destination_id = p.partition_number
JOIN sys.filegroups fg
ON dds.data_space_id = fg.data_space_id
JOIN (SELECT container_id, sum(total_pages) as total_pages
FROM sys.allocation_units
GROUP BY container_id) AS au
ON au.container_id = p.partition_id
–WHERE i.index_id <2;
WHERE OBJECT_NAME(i.object_id) = ‘tabelaXPTO’