Recent comments

None


İçerik Ara











Yasal Uyarı
Bu sitede sunulan tüm bilgi ve dökümanlar Turgay Sahtiyan tarafından yazılmaktadır. Yazıların kaynak göstermek şartıyla kullanılması serbesttir.

© Copyright 2009-2013
Takvim
<<  Aralık 2017  >>
PaSaÇaPeCuCuPa
27282930123
45678910
11121314151617
18192021222324
25262728293031
1234567
Keywords

Recently one of my customer needs to query all tables’ sizes and row counts in order to track these information in a table periodically and determine the growth trend.

[more]

Here is the script;

create table #tmp1 (
      Servername SysName,
      DBName SysName,
      object_id int,
      ScheamaName sysName,
      TableName sysName,
      TableSize_KB int,
      row_count int
)

create table #tmp2 (
      Servername SysName,
      DBName SysName,
      object_id int,
      ScheamaName sysName,
      TableName sysName,
      row_count int
)

exec sp_msforeachdb 'use ?;
if DB_ID()>4 begin
      insert #tmp1
      select @@ServerName,DB_Name(),ps.object_id,
                  schema_name(o.schema_id) as SchemaName,
                  o.name as TableName,
                  SUM(ps.reserved_page_count)*8 as TableSize_KB,
                  0 as row_count
      from sys.dm_db_partition_stats ps
      join sys.objects o on o.object_id = ps.object_id
      where o.type=''U''
      group by ps.object_id,o.schema_id, o.name

      insert #tmp2
      select @@ServerName,DB_Name(),ps.object_id,
                  schema_name(o.schema_id) as SchemaName,
                  o.name as TableName,    
                   sum(ps.row_count) as row_count
      from sys.dm_db_partition_stats ps
      join sys.objects o on o.object_id = ps.object_id
      where o.type=''U'' and ps.index_id in (0,1)
      group by ps.object_id,o.schema_id, o.name
end
';


update t1
set t1.row_count=t2.row_count
from #tmp1 t1
left join #tmp2 t2 on 
      t2.ServerName=t1.ServerName and
      t2.DBName=t1.DBName and
      t2.object_id=t1.object_id


select * from #tmp1
order by TableSize_KB desc

drop table #tmp1
drop table #tmp2

And a sample result like that;

image

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


Bazı ürün geliştirmeleri ve müşteri problemlerinin çözüme olarak geliştirilen HotFix’leri içeren SQL Server 2008 R2 SP2 bugün yayınlandı.

Önemli maddeler aşağıdaki gibi;

  • Reporting Services Charts Maybe Zoomed & Cropped
    Customers using Reporting Services on Windows 7 may sometime find charts are zoomed in and cropped. To work around the issue some customers set ImageConsolidation to false.
  • Batch Containing Alter Table not Cached
    In certain situations with batch files containing the alter table command, the entire batch file is not cached.
  • Collapsing Cells or Rows, If Hidden Render Incorrectly
    Some customers who have hidden rows in their Reporting Services reports may have noticed rendering issues when cells or rows are collapsed. When writing a hidden row, the Style attribute is opened to write a height attribute. If the attribute is empty and the width should not be zero.

Download için aşağıdaki linkleri kullanabilirsiniz.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


During DML (insert, update, delete) operations, every related indexes are updated. If the table has too many indexes, SQL Server will spend a lot of resource and time for these operations and it might be a performance problem.

[more]

You can use the below script to identify which tables have more indexes than column count.

create table #tmp1 (DBName sysName, SchemaName sysName, ObjectName sysName, IndexCount int, ColumnCount int)

exec sp_msforeachdb 'use ?;
insert #tmp1
select ''?'' as DBName, SchemaName, ObjectName, IndexCount, count(*) as ColumnCount
from (
      select object_schema_name(object_id) as SchemaName
            ,object_name(object_id) as ObjectName
            ,count(*) as IndexCount       
            ,object_id
      from sys.indexes i
      where ObjectProperty(object_id,''IsUserTable'')=1 and index_id>0 
      group by object_id
)xx   
join sys.columns c on c.object_id=xx.object_id
group by SchemaName, ObjectName, IndexCount
having IndexCount>count(*)
'

select * from #tmp1

drop table #tmp1
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


If a table does not have clustered index then we call this table as “Heap Table”

For OLTP system, most of time, it is better to use Clustered Index instead of Heap Table for performance. If a table does not have clustered index, when a new row inserting, SQL engine try to find a page to locate this row by using PFS pages. And this behavior might be a performance issue. On the other hand, if the table has clustered index, then SQL engine already knows where the new record might have been located. Because clustered index define the physical order of the rows.

[more]

The second reason might be “forwarded record” issue. Forwarded record is a Heap Table issue. To avoid this issue, we should create clustered index.

And also, heap tables are out of the index maintenance operation. Index fragmentation is really important to get better performance from queries. So third reason might be that.

Finally, if you decided to create clustered index you need a script to identify heap tables. You can use the below script for this purpose.

create table #HeapTables(			
	DBName sysname, 		
	Objectid int, 		
	SchemaName sysname, 		
	TableName sysname,		
	TableRowCount int,		
	NonClusterIndexCount int,		
	TableHasIdentity smallint,		
	IdentityColumnName sysName null		
	)		
			
exec sp_MSforeachdb 'use ?;			
	insert #HeapTables		
		select DB_NAME() as DBName,t.object_id,schema_name(t.schema_id) as SchemaName,t.name as TableName	
			,(select sum(row_count) from sys.dm_db_partition_stats where object_id=t.object_id and index_id in (0,1)) as TableRowCount 
			,(Select count(1) from sys.indexes i where i.object_id=t.object_id and index_id>1) as NonClusterIndexCount
			,objectproperty(t.object_id,''TableHasIdentity'')
			,(Select name from sys.columns c where c.object_id=t.object_id and is_identity=1) as IdentityColumn
		from sys.tables t	
		where t.type=''U'' and objectproperty(t.object_id,''TableHasClustIndex'')=0	
'			
			
select * from #HeapTables			
where DBNAME not in ('master','model','msdb','tempdb')			
order by DBName, SchemaName, TableName			
			
drop table #HeapTables			

Sample output might be like that;

image

You can take the last 2 columns as advice. Identity columns are good candidate to be clustered index key. So if you don’t have better option you can use this identity column as clustered index key.

Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


One of my customers had a strange issue with one of their database’s log file.

The database is in simple recovery model but log file keep growing and committed transactions not truncated automatically.

First of all I thought that there was an open transaction on this database and this transaction block the truncation. I have checked and there was no open transaction.

Then I thought it might be a good idea to see what happens when getting a log backup. So I have changed the recovery model to full and tried to get a log backup.

I got a message;

The log was not truncated because records at the beginning of the log are pending replication or Change Data Capture. Ensure the Log Reader Agent or capture job is running or use sp_repldone to mark transactions as distributed or captured.

And also sys.databases.log_reuse_wait_desc column’s value was “REPLICATION”.

It is weird. Because there is no replication setup for this database and this database has never been published.

Here is the resolution;

  1. Setup replication for this database
  2. Run this script; sp_repldone NULL, NULL, 0, 0, 1
  3. Take log backup
  4. See the log is truncated
  5. Remove the replication
  6. If you want, shrink the log file
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan


SQL Server Management Studio üzerinden bir database’in özelliklerine girmek istediğinizde aşağıdaki gibi bir hata ile karşılaşabilirsiniz.

[more]

clip_image001

Cannot show requested dialog
Property Owner is not available for Database '[coda]'. This property may not exist for this object, or may not be retrievable due to insufficient access rights. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.1399.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.

Bu hatanın sebebi database’in owner’ının geçerli bir user olmamasıdır.

Database’in owner’ini aşağıdaki kod vasıtası ile değiştirerek hatadan kurtulabilirsiniz.

USE DBName
EXEC sp_changedbowner 'sa'
Not : Blog haricinde, faydali gördügüm yazilari ve linkleri twitter adresimden paylasiyorum. Beni twitter'da takip etmek için : twitter.com/turgaysahtiyan