Back & Restore:
- 253817 – How to back up the last transaction log when the master and the database files are damaged in SQL Server
- 2019698 – How to schedule and automate backups of SQL Server databases in SQL Server Express Editions
- 2023538 – How to setup the custom log shipping which has a RESTORE VERIFY ONLY step
- 968872 – How do I open the firewall port for SQL Server on Windows Server 2008?
- 823938 – How to configure an instance of SQL Server to listen on a specific TCP port or a dynamic port
- 244980 – How to change the network IP addresses of SQL Server failover cluster instances
- 318605 – How SQL Server uses a certificate when the Force Protocol Encryption option is turned on
- 294209 – How to rebuild or move a MSDTC installation to be used with a SQL failover cluster
- 811889 – How to troubleshoot the “Cannot generate SSPI context” error message
- 295732 – How to create databases or change disk file locations on a shared cluster drive on which SQL Server was not originally installed
- 237980 – How to convert an Access database to SQL Server
- 306397 – How to use Excel with SQL Server linked servers and distributed queries
- 315521 – How to troubleshoot SQL Server merge replication problems.
- 198891 – How to run a DLL-based COM object outside the SQL Server process
- 231619 – How to use the SQLIOSim utility to simulate SQL Server activity on a disk subsystem
- 908018 – How to create a package template in SQL Server Business Intelligence Development Studio
- 912426 – How to detect the status of the SQL Server Express service or start the SQL Server Express service by using Visual Basic or Visual C#
- 945934 – How to register Microsoft Filter Pack IFilters with SQL Server 2005 and with SQL Server 2008.
Best practices: SQL 2008
- Troubleshooting Performance Problems in SQL Server 2008 White Paper very handy.
- 982870 – Diagnosing and Troubleshooting SQL Server Performance Problems
- Server Memory Options
- Memory Architecture
- Disk Partition Alignment Best Practices for SQL Server
- Optimizing the paging file size and location
- Physical Database Storage Design
- Monitoring Disk Usage
- Statistics Used by the Query Optimizer in Microsoft SQL Server 2008
- Scaling Up Your Data Warehouse with SQL Server 2008
- Improving Performance with SQL Server 2008 Indexed Views
- Checklist: Prepare SQL Server for peak workloads
- How Many Data Files Should I Create for a User Database? – by Linchi Shea
- INF: Understanding and Resolving SQL Server Blocking Problems
- Managing TempDB in SQL Server, Part I and Part II
- SQL Server Performance Team Blog
- 980653 – SQL Server 2008 R2 Cumulative Update 2, SQL Server 2008 SP1 Cumulative Update 7 and SQL Server 2005 SP3 Cumulative Update 9 introduce trace flag 4136 that can be used to disable the “parameter sniffing” process
Very well known problems in SQL Server 2005, 2008 in 32bits or 64bits:
- 316749 – There may not be enough virtual memory when you have a large number of databases in SQL Server
- 906892 – You may experience slow performance when you run 32-bit SQL Server tools on 64-bit operating systems
- 929728 – An instance of SQL Server performs slowly when you use SQL Profiler to perform a client-side trace of the events in the instance
- 931279 – SQL Server timing values may be incorrect when you use utilities or technologies that change CPU frequencies
- 951935 – Query performance is slow when you run a query that contains an ORDER BY clause on a table column of an SQL tertiary collation
- 2000380 – SQL Server 2005 and SQL Server 2008 reports 701 errors with large batch sizes
- 2001221 – SQL Server reports 701: “There is insufficient memory to run this query” when executing large batches
- 2000395 – You may notice performance issues when using date literal values in distributed queries with third party OLEDB providers from SQL server.
- 2003031 – Query performance decreases when sys.dm_db_index_usage_stats has large number of rows
- 2009160 – High CPU utilization may occur with your queries on SQL Server 2005 and SQL Sever 2008 servers compared to SQL Server 2000
- 2028436 – Certain database operations take a very long duration to complete or encounter errors when the transaction log has numerous virtual log files
- 2413549 – Using large amounts of memory can result in an inefficient plan in SQL Server
- 2407439 – You may notice an increased transaction log sizes in SQL Server 2008 and later versions.
Best way to learn is share knowledge, any comments are welcome here.
SQL & SPS DBA – MCTS