SQL and ML Spaces

Multiple ways to count the number of records in a table of SQL server:

1. select count(*) or select count(id) or select count (distinct id)  : if we want the exact number but it is an additional overhead as it does table scan or index seek depending on the column chosen.

2. Sysindexes: The columns that need be taken into account are id, indid, rowcnt

indid can take various values as shown below:

0 – no clustered index

1 – CI

indid > 1 and indid < 255 – non CI

255 – text/image datatypes

Query to find the number of rows using sysindexes:

[cc lang=”SQL”]

SELECT so.[name] as [table name]
     , CASE WHEN si.indid between 1 and 254
            THEN si.[name] ELSE NULL END 
            AS [Index Name]
     , si.indid 
     FROM sysindexes si
          INNER JOIN sysobjects so
             ON si.id = so.id
     WHERE si.indid < 2 
       AND so.type = ‘U’ 
       AND so.[name] != ‘dtproperties’
     ORDER BY so.[name]

[/cc]

If the statistics are not updated, above number might not be exact. So update statistics using

[cc lang=”SQL”]

dbcc updateusage

[/cc]

It gives us the ability to refer to the objects in in another schema or database or using linked server to remote server too avoiding the 4 part naming convention.

Roles help in maintaining security in all components of SQL server.

To configure SQL Server job agent fixed server roles, we need to navigate to msdb –> security –> roles –> DB roles.
SQL agent roles:

SQLAgentUserRole : Ability to manage Jobs that they own

SQLAgentReaderRole: Has additional rights than SQLAgentUserRole and also to monitor multiserver jobs

SQLAgentOperatorRole: SQLAgentReaderRole rights in addition to modify operators, proxy and alert. Delete job history and modify the schedule of local jobs.

Most of the information related to SQL Server or DB are stored in the system catalogs like sys tables or information_schema views.

sys.servers – local, remote and linked servers
sp_helpserver – SQL Server metrics
sp_configure – server wide configurations like DOP, processors etc.,
sp_serveroption – Configures server options for remote or linked servers
SELECT SERVERPROPERTY(‘Edition’) – collation, edition, instance name, security configuration, etc

sp_server_info – Approximately 30 connection related parameters primarily for ODBC
SELECT @@SERVERNAME – return the SQL Server instance name
sp_helpsort – System stored procedure to return the sort order and character set for the SQL Server
sp_who2 – active processes
sys.dm_exec_sessions – process (spid) is active, sleeping, etc

— Understand the performance of SQL server at macro level
— use sys.dm_os_performance_counters

Counters: sample counters that can be used are:
MSSQL:SQL Errors
MSSQL:SQL Statistics
MSSQL:Locks
MSSQL:Memory Manager
MSSQL:General Statistics
MSSQL:Transactions
MSSQL:Wait Statistics
etc.,

Now the limitation is that all above counters are specific to SQL Server. If you still want to capture relating to disk or network – like physical disk, network interface card, etc then use performance monitor.