Browse by Tags

• 823
• 8966
• allocation
• application
• binary
• blocking
• checkdb
• clr
• component
• corrupted
• cte
• data
• data type
• database
• databases
• dba
• dbcc
• deadlock
• defrag
• delete
• dts
• extent
• fragmentation
• Hardware
• high cpu
• i/o
• index
• indexes
• insert
• join
• kba
• locking
• loss
• oltp
• package
• page
• performance
• process
• profiler
• query
• queue
• recursive
• requirement
• rowcount
• security
• showcontig
• space
• SQL Server
• ssis
• SSMS
• statistics
• string
• sysindexes
• trace
• transact sql
• transactions
• trigger
• truncated
• tsql
• wait

• How to get rowcount efficiently within a SSIS package?

  I would like to call this as how efficiently you can obtain counting rows in SSIS package. Usual thought would come is @@rowcount or COUNT_BIG functions, but this is not that efficient way to obtain the results. SSIS provides the snippet within its components Read More... Posted Wednesday, September 05, 2007 6:50 PM from SSQA.net | (Comments Off) Filed under: SQL Server, databases, table, ssis, package, component, rowcount, requirement

• DBCC EXTENTINFO - unused pages in these extents (undocumented)

  When it comes to resolving table fragmentation, the basic checkout you perform is to run DBCC INDEXDEFRAG or even run DBCC DBREINDEX statements. As per the default configuraiton SQL Database Engine allocates a new extent to an allocation unit only when Read More... Posted Tuesday, September 04, 2007 10:45 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, indexes, fragmentation, table, deadlock, i/o, insert, data, allocation, extent

• Triggers within CLR - advantage over TSQL

  You may be aware the DML and DDL triggers can be nested up to 32 levels, because any reference to such trigger code counts as one-level in the nesting limit. Even though it is possible to control whether AFTER triggers can be nested through the nested Read More... Posted Tuesday, September 04, 2007 7:10 PM from SSQA.net | (Comments Off) Filed under: cte, SQL Server, performance, query, tsql, clr, table, trigger, recursive

• Performance - check whether it is affecting application or system, don't blame SQL Server alone

  Its a general assumption that whenever performance is degraded, the finger will be pointed to SQL Server, pretty easy eh!? If you look at any of the SQL Server related forums then 3 in 10 questions asks same question and nothing but shove blame on SQL Read More... Posted Tuesday, September 04, 2007 12:42 AM from SSQA.net | (Comments Off) Filed under: security, SQL Server, performance, query, blocking, table, application, wait, queue, high cpu, oltp

• Failed to claim unused space with SP_SPACEUSED?

  Recently I have been stumped by not cliaming unused space correctly even though after delete handful of rows on a table. You may be aware using SP_SPACEUSED will get you details such as : Column name Data type Description database_name nvarchar(128) Name Read More... Posted Friday, August 31, 2007 7:09 PM from SSQA.net | (Comments Off) Filed under: SQL Server, database, table, dba, dbcc, space, transact sql, data type, delete

• Rebuild some fragmented indexes - problem with Extent Scan Fragmentation value

  This was the question from SSP forums, I’m trying to rebuild few fragmented indexes on a table that is updated on regular basis. The “dbcc showcontig” gave this result: Table: 'Confidential' (999999999); index ID: 5, database ID: 5 LEAF level scan performed. Read More... Posted Friday, August 24, 2007 9:24 PM from SSQA.net | (Comments Off) Filed under: security, SQL Server, performance, fragmentation, table, dbcc, index, showcontig, defrag

• FIX: Error message when you try to edit a SQL Server Agent job or a maintenance plan by using SQL Server Management Studio in SQL Server 2005: "String or binary data would be truncated"

  The following pages were recently modified. Source: Knowledge Base Product: Microsoft SQL Server 2005 Standard Edition, SQL Server 2005 Enterprise X64 Edition & SQL Server 2005 Enterprise Edition Notification Contents: New and All Modifications FIX: Read More... Posted Friday, August 24, 2007 6:17 AM from SSQA.net | (Comments Off) Filed under: SQL Server, SSMS, table, kba, data, binary, truncated, string

• Error: Could not find row in sysindexes for database. 8966, 823 and 602

  Long ago, not long ago.... No doubt that many of you might have gone through the error above within your SQL environment, also I see many forum posts out there to resolve the issue. The bottom line of this issue is Hardware and no other issue can contribute Read More... Posted Tuesday, August 14, 2007 7:35 PM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, Hardware, database, table, corrupted, dbcc, sysindexes, 8966, 823, page, checkdb

• DTS package, how will optimizer treats the transactions?

  With the SQL Server 2000 DTS has very good capability of treating the huge processes as transactions, let's assume these parameters: DTS Package with "Use Transactions" and "Commit on successful package completion" boxes checked. No transaction related Read More... Posted Thursday, August 02, 2007 10:52 PM from SSQA.net | (Comments Off) Filed under: performance, statistics, table, dts, transactions, join

• Table fragmentation, Index contention and locking issues to resolve

  Yet another important factors that every DBA needs to concerned about table & index fragmentation within their SQL Server environment. Refer to the a rticle about DetectTableFragmentation in both 2000 and 2005 version. Fragmentation occurs due to Read More... Posted Wednesday, August 01, 2007 12:31 AM from SSQA.net | (Comments Off) Filed under: SQL Server, performance, blocking, profiler, locking, trace, process, loss, fragmentation, table