:::: MENU ::::

Posts Categorized / Monitoring and Analysis

  • May 21 / 2009
  • 0
DBCC Commands, dbDigger, Monitoring and Analysis

List SET options for current connection through DBCC USEROPTIONS

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


Options are listed in following format

List of SET options for current connection

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
  • May 19 / 2009
  • 0
dbDigger, Monitoring and Analysis, System Stored Procedures

Get list of all databases through SP_DATABASES

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.


Get list of all databses on SQL Server instance
From here is copied names of required databases to be dropped.

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger, Monitoring and Analysis

Logging and Error Reporting for SQL Server Analysis 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.

Query Log

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.

  • May 13 / 2009
  • 0
Analysis Services SSAS, dbDigger, Monitoring and Analysis

Processor usage for Analysis Server

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.

Consult us to explore the Databases. Contact us