While going through optimization processes of some tables, i came across a very helpful article to get insight in Index usage. This Article on MSSQLTips.com has various scripts for analysis of usage of Indexes.
Often i am required to verify any of batch level options for my current connection. This task may be easily completed through use of informational DBCC command (DBCC USEROPTIONS) in following way
DBCC USEROPTIONS GO
Options are listed in following format
Also it is relevant to mention here that options may be of
- SQL Server instance level
- SQL Server database level
- T-SQL batch level
- T-SQL Statement level
OLEDB applications can control some ANSI options through their connection options. Microsoft recommends not to change SET options through SET statements. Instead using the connection properties of ODBC/OLEDB or system stored procedure sp_configure is preferable.
- A database level option overrides the instance option
- Batch level option overrides database option
- Statement level option overrides batch level options
While planning to drop many of my test databases, i was tired to write name of all such databases. While looking for any stored procedure to get list of all databases on my SQL Server instance, i found SP_DATABASES. It provided me complete list of databases along with over all size statistics for each.
From here is copied names of required databases to be dropped.
My article related to Analyzing SQL Agent Jobs and Jobs Steps History in SQL Server is published on following address. Due to copy right restrictions i will not publish it here. You may visit it at Analyzing SQL Agent Jobs and Jobs Steps History in SQL Server
Analysis Services records a query log to enable you to analyze query patterns and improve your aggregation design. You can configure the properties of this query log. You can also enable a processing log and enable Analysis Services error reporting.
To enable the Usage Based Optimization Wizard to design aggregations based on past usage patterns and to enable the Usage Analysis Wizard to generate reports analyzing query usage, Analysis Services records the levels touched by every Nth query in a query log. By default, every tenth query is logged. The default location for the query log is C:Program FilesMicrosoft Analysis ServicesBinmsmdqlog.mdb. This file, like any log file, should be secured from unauthorized access.
If you are running Analysis Services on a multiple processor computer, Analysis Services schedules threads on all available processors in the computer. Unlike the SQL Server service, Analysis Services does not natively support processor affinity to control the processors on which its threads will execute. Because Analysis Services is highly multithreaded, Analysis Services can consume all available processing resources. For this reason, you should use a dedicated server for Analysis Services in most cases. If you must share the computer resources with other server applications, you should select a server application that supports processor affinity, such as SQL Server. By setting processor affinity in SQL Server, you can control the processors executing the SQL Server threads and the priority of these threads, to ensure that sufficient processor resources remain available for Analysis Services threads.
If you need to control the processors on which Analysis Services threads execute, you should also consider using Microsoft Windows Server™ 2003 Enterprise Edition or Windows Server 2003 Datacenter Edition. These editions of Windows Server 2003 include the Windows System Resource Manager (WSRM), which allows an administrator to set processor and memory allocation policies for applications running on the server. WSRM enables you to select the Analysis Services process and limit Analysis Services threads to specific CPUs or to a specific threshold of processor resources. Compared to a transactional database, OLAP systems typically take more space for the sort area but less space for the rollback area. Most transactions in an OLAP system take place as part of a batch process. Instead of having several rollback areas for user input, you may resort to one large rollback area for the loads, which can be taken offline during daily activity to reduce overhead.