Browse by Tags

• backups
• baseline
• best practice
• buffer
• busy
• certificate
• configuration
• connect
• cpu
• create
• creation
• databases
• dbcc shrinkfile
• disk space
• dmv
• entry
• force
• guide
• hardware
• huge
• i/o
• index
• indexes
• integration
• management
• memory
• monitoring
• online
• operating system
• optimization
• package
• parallel execution
• partition
• perfmon
• performance
• plan
• query
• raid
• ratio
• registry
• security
• ssas
• ssis
• ssms
• sysmon
• system
• tablesample
• temp tables
• tempdb
• top
• troubleshoot
• tsql
• users
• workfiles
• worktables

SQL Server - Have you observed DBCC SHRINKFILE operation performance, on huge databases?
25 April 08 01:30 AM | SQL Master | 2 Comments   

In general it is not a best practice to perform SHRINK database operation on a production server, atleast regularly! Sometimes it may be compulsory to keep them sized in order to ensure the disk storage is not compromised for any sudden changes to databases Read More...

Filed under: sql server, cache, memory, indexes, databases, cpu, monitoring, operating system, huge, dbcc shrinkfile

SQL Server Memory - what's your method on Configuration and Troubleshooting Issues
01 April 08 02:49 AM | SQL Master | 5 Comments   

Memory - an important aspect of system performance within a RDBMS platform, not specific to a database product or application. Coming to Microsoft related products such as Windows Server and SQL Server so on, various resources available on web such as Read More...

Filed under: performance, sql server, cache, memory, troubleshoot, raid, hardware, best practice, disk space, monitoring, system, baseline, configuration

SQL Server 2005 PERFMON counter spikes in Transactions/sec and Buffer Cache Hit ratio, what does it say?
14 March 08 02:16 AM | SQL Master | 3 Comments   

Recently I was involved in one of the Performance Audit exercise at a client's place where they complain about Server CPU is always HIGH and what they have observed is even the physical disk where TEMPDB is located has been used extensively, as they can Read More...

Filed under: dmv, perfmon, buffer, sysmon, tempdb, monitoring, temp tables, workfiles, worktables, ratio, performance, sql server, cache

SQL Server 2005 Enterprise Edition - make data available while creating indexes on large tables!
25 January 08 02:41 AM | SQL Master | 3 Comments   

Though it is not a best practice to perform a CREATE INDEX on large tables during the online hours where you have number of users accessing the metadata of SQL Server database. Within SQL Server 2005 Enterprise Edition you can perform ONLINE indexes operation, Read More...

Filed under: performance, sql server, cache, indexes, tsql, users, best practice, backups, monitoring, operating system, creation, create, online, busy

Considering Parallel plans and forcing plans for better performance
23 October 07 06:55 AM | SQL Master | 2 Comments   

Parameterizing T-SQL queries are a well-known task such as database programming and best practice in some tasks. It allows query plan reuse and eliminates the need of recompilation for multiple invocations of the same query that simply has different parameter Read More...

Filed under: performance, sql server, cache, optimization, cpu, index, partition, parallel execution, force, guide

How to configure SQL servers to use certificates?
03 September 07 04:50 AM | SQL Master | 2 Comments   

Continuation to the BlogPost on issuing or configuring SQL Servers to use certificates, I have found useful reference on Technet blogs - Mr. Cert’s side of the story . Read More...

Filed under: sql server, security, cache, certificate, registry

List of CPU based DMVs in SQL Server 2005 - best used for hardware analysis
05 August 07 12:32 PM | SQL Master | 5 Comments   

Dynamic Management Views (DMV) are very useful to get server state information to monitor the status, but not always you have to still continue in monitoring using SYSMON & PROFILER that are available by default with SQL installation. The engine within Read More...

Filed under: performance, sql server, cache, dmv, cpu, i/o

SQL Server 2005 Query optimization issue : limit affected rows on a large volume table?
10 July 07 03:40 AM | SQL Master | 1 Comments   

If you have a huge volume of table (say millions of rows) and a requirement to limit x number or sample of rows from a query every time you execute. To limit number of rows then you would think about [ select ... from (select top 100 * from X order by Read More...

Filed under: performance, sql server, query, cache, top, tablesample

What events will cause a flushing of plan in cache?
14 May 07 05:17 AM | SQL Master | 3 Comments   

We already know that using DBCC FREEPROCCACHE will have the affect of flushing all the cache with immediate affect and also DBCC FREESYSTEMCACHE (new in SQL 2005) has similar affect but will flush the plans asynchronously that are marked as unused anymore. Read More...

Filed under: sql server, security, management, plan, cache, memory, perfmance

Reset cached plan - how to and what to?
14 May 07 01:53 AM | SQL Master | 1 Comments   

I don't believe you would need to reset the cache on a production server, it is not a best practice to perform in this case. Most of the times in development or test environments for the purpose of benchmarking you would need to reset the cache for optimum Read More...

Filed under: sql server, plan, integration, package, cache, memory, indexes, perfmance

Taking advantages of Cryptographic features in SQL 2005 - security
12 May 07 03:16 AM | SQL Master | 1 Comments   

When I had a chance to know about encryption features in SQL Server I always go with books online as a first hand information, but there is more to ask about this feature in 2005 version: Have you ever thought about encryption in SQL Server, how to implement Read More...

Filed under: sql server, security, integration, ssis, cache, entry, ssas

Cached query plans and top stored procedures that are recompiled
11 May 07 04:02 AM | SQL Master | 4 Comments   

We all know that the cached query plans are good to go to attain the performance and no doubt that inefficient query plan will cause more distress to performance with an increased spike in CPU consumption. At the same occurrence of recompilation of a Read More...

Filed under: sql server, query, plan, package, cache, indexes, perfmance

Recompile all the stored procedures
27 April 07 06:42 AM | SQL Master | 0 Comments   

There has been many instances of requests from the Developers to recompile a set of Stored Procedures on one of the mission critical databases, due to the performance issues the relevant SPs have been fine tuned for optimum performance. In order to take Read More...

Filed under: ssms, performance, sql server, connect, management, cache