Archive for the SQL Server Category

SQL Server Performance Monitor Counters

Posted in SQL Server with tags , , , , , on January 1, 2012 by DBAWork

As we are aware SQL server has certain counters which can be monitored to have better performance using SQL server. We will try to elaborate on all counters. These are counters we need to monitor on regular basis.

We will see what is meaning of Batch Requests/Sec counter, how to read it from SQL server.

To get a feel of how eventful SQL Server is, observer this SQLServer counter. This counter processes the number of batch demands that SQL Server receives per second, and largely follows in step to how hectic your server’s CPUs are. Usually speaking, over 1000 batch requests per second specifies a very eventful SQL Server, and could mean that if you are not already suffering a CPU bottleneck, that server may very well soon. Unquestionably, this is a comparative number, and the higher your server hardware, the additional batch requests per second SQL Server can serve.

From a network traffic jam approach, a classic 100 Mbs network card is only able to handle about 3000 batch requests per second. If you have a server that is this demanding, you may need to have two or more network cards, or go to a Gbs network card.

Several DBAs use the Transaction/Sec Total to measure total SQL Server commotion, but this is  a bad idea. Transaction/Sec only processes action that is inside a transaction, not all movement, producing crooked results. In its place, continuously use the SQLServer: SQL Statistics: Batch Requests/Sec counter, which events all SQL Server commotion.

One by one we will see all these counters

DBAWorkDatabase support, Database services, Oracle ADF

 

Advertisements

SQL Server 2008 – New Features

Posted in SQL Server with tags on August 18, 2011 by DBAWork

Transparent Data Encryption

Empower encryption of an complete database, data files, or log files, without the requisite for application deviations. Benefits of this include: Search encrypted data using both range and fuzzy searches, exploration secure data from unapproved users, and data encryption without any mandatory variations in existing applications

Extensible Key Management

SQL Server 2005 provides a wide-ranging solution for encryption and key managing. SQL Server 2008 transports an exceptional solution to this increasing need by supporting third-party key management and HSM merchandises.

Assessing

Craft and manage inspecting via DDL, while simplifying acquiescence by providing more comprehensive data auditing. This enables establishments to answer common questions, such as, “What data was recovered?”

Improved Database Mirroring

SQL Server 2008 builds on SQL Server 2005 by providing a supplementary trustworthy platform that has enriched database mirroring, including spontaneous page repair, enhanced performance, and improved supportability.

Automatic Recovery of Data Pages

SQL Server 2008 enables the foremost and mirror machines to evidently recover from 823/824 types of data page miscalculations by requesting a fresh copy of the suspect page from the mirroring partner transparently to end users and applications.

Database SQL server Support, DBAWorkDatabase support, Database services, Oracle ADF

System Databases – SQL Server

Posted in DB2, General, Oracle, SQL Server with tags on June 3, 2011 by DBAWork

Microsoft SQL Server is a relational database server developed by Microsoft : It is a software product whose primary function is to store and retrieve data as requested by other software applications, be it those on the same computer or those running on another computer across a network (including the Internet). There are at least a dozen different editions of Microsoft SQL Server aimed at different audiences and for different workloads (ranging from small applications that store and retrieve data on the same computer, to millions of users and computers that access huge amounts of data from the Internet at the same time).

Master DB

Records all the system-level information for an instance of SQL Server.
This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings.
Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server.
Therefore, SQL Server cannot start if the master database is unavailable.

msdb DB

It is used by SQL Server Agent for scheduling alerts and jobs and by other features such as Service Broker and Database Mail.

model DB

Is used as the template for all databases created on the instance of SQL Server. Modifications made to the model database, such as database size, collation, recovery model, and other database options, are applied to any databases created afterward.

ResourceDB

Is a read-only database that contains system objects that are included with SQL Server. SQL Server system objects, such as sys.objects are physically persisted in the Resource database, but they logically appear in the sys schema of every database.

Tempdb DB

It is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:

  • Temporary user objects that are explicitly created, such as: global or local temporary tables, temporary stored procedures, table variables, or cursors.
  • Internal objects that are created by the SQL Server Database Engine, for example, work tables to store intermediate results for spools or sorting.
  • Row versions that are generated by data modification transactions in a database that uses read-committed using row versioning isolation or snapshot isolation transactions.
  • Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.

Operations within tempdb are minimally logged. This enables transactions to be rolled back. tempdb is re-created every time SQL Server is started so that the system always starts with a clean copy of the database. Temporary tables and stored procedures are dropped automatically on disconnect, and no connections are active when the system is shut down. Therefore, there is never anything in tempdb to be saved from one session of SQL Server to another. Backup and restore operations are not allowed on tempdb.

Distribution

Database responsible for the replication metadata between the publisher and subscriber(s)

ReportServer

Primary database for Reporting Services to store the metadata and object definitions

  • Reports security
  • Job schedules and running jobs
  • Report notifications
  • Report execution history

ReportServer TempDB

Temporary storage for Reporting Services

Session information

Cache

Remote database support, DBAWorkDatabase support, Database services, Oracle ADF