* Difference between LazyWriter and Checkpoint

Lazywriter:  

ü  Flush dirty pages to disk.

ü  Check for available memory and removed Buffer pool.

ü  Occurs depending upon memory pressure and resource availability

ü  Name says Lazy so it’s lazy, Sql server manages by its own.

ü  Monitor the memory pressure and try maintaining the available free memory.

ü  No role in recovery

ü  It occurs per requirement

ü  Works on Least recent used pages and removed unused plans first, no user control.

ü  No effect on recovery model.

ü  No command for Lazy Writer

ü  No performance impact

Checkpoint:

ü  Flush only Data pages to disk

ü  Default, Occurs for every 1 minute

ü  Can be managed with sp_ configure  recovery interval option

ü  Does not check the memory pressure

ü  Crash recovery process will be fast to read log as data file is updated.

ü  Occurs for any DDL statement and before Backup/Detach command

ü  Depends upon the configuration setting, we can control.

ü  For simple recovery it flushes the t-log file after 70% full.

ü  Can manually /Forcefully run command “Checkpoint”

ü  Very Less performance impact

* Removing all active connections from the db

Go

Declare @dbname sysname

Set @dbname = 'DBNAME'

Declare @spid int Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname)

While @spid Is Not Null Begin

Execute ('Kill ' + @spid)

Select @spid = min(spid) from master.dbo.sysprocesses

where dbid = db_id(@dbname) and spid > @spid

End

SQL Server 2008 R2 SP2 CTP

SQL Server 2008 R2 SP2 CTP is now available. For more information, please click the link below..

SQL Server 2008 R2 SP2 CTP

Find blocking using DMV

Blocking in SQL Server 2005

Prior to SQL Server 2005, blocking could be detected using the sp_blocker_pss80 stored procedure, sp_who2, Perfmon and SQL Profiler. However, SQL Server 2005 has added some important features to the System Monitor and new DMVs for diagnosing the same. 

• Enhanced System Monitor counters (Perfmon)
• DMV's: sys.dm_os_wait_stats, sys.dm_os_waiting_tasks and sys.dm_tran_locks

In System Monitor, the Processes Blocked counter in the SQLServer:General Statistics object shows the number of blocked processes. The Lock Waits counter from the SQLServer:Wait Statistics object can be added to determine the the count and duration of the waiting that is occurring. The Processes blocked counter gives an idea of the scale of the problem.

            DMV's such as sys.dm_os_waiting_tasks and sys.dm_tran_locks give accurate and detailed blocking information. The sys.dm_os_waiting_tasks DMV returns a list of all waiting tasks, along with the blocking task if known. There are a number of advantages to using this DMV over the sp_who2 or the sysprocesses view for detecting blocking problems:

                               In the above screenshot the blocking_session_id shows the blocking process SPID.

SQL Server 2012 Editions

SQL Server 2012 Editions

             

                   SQL Server 2012 is obtainable in three main editions. All three editions have tighter alignment than their predecessors and were designed to meet the needs of almost any customer with an increased investment in business intelligence. Each edition comes in a 32-bit and 64-bit version. The main editions,

• Enterprise edition
• Standard edition
• Business Intelligence edition

Enterprise Edition

          The Enterprise edition of SQL Server 2012 is the uppermost SKU; it is meant to meet the highest demands of large-scale datacenters and data warehouse solutions by providing mission-critical performance and availability for Tier 1 applications, the ability to deploy private-cloud, highly virtualized environments, and large centralized or external-facing business-intelligence solutions.

The Enterprise edition features include the following:

     Maximum number of cores is subject to the operating system being used

     Advanced high availability can be achieved with AlwaysOn

     Unlimited virtualization if the organization has software insurance

     Support for the new columnstore indexing feature

     Advanced auditing

     Transparent Data Encryption (TDE)

     Compression and partitioning

     Includes all of the Business Intelligence edition’s features and capabilities:

     Reporting

     Analytics

     Multidimensional BI semantic model

     Data-quality services

     Master data services

     In-memory tabular BI semantic model

     Self-service business intelligence

Standard Edition

          The Standard edition is a data-management platform tailored toward departmental databases and limited business-intelligence applications that are typically appropriate for medium-class solutions, smaller organizations, or departmental solutions. It does not include all the bells and whistles of the Enterprise and Business Intelligence editions; however, it continues to offer best-in-class manageability and ease of use. Compared to the Enterprise and Business Intelligence editions, the Standard edition supports up to 16 cores and includes the following:

     Spatial support

     FileTable

     Policy-based management

     Corporate business intelligence

     Reporting

     Analytics

     Multidimensional BI semantic model

     Basic high availability can be achieved with AlwaysOn 2-Node Failover Clustering

     Up to four processors, up to 64 GB of RAM, one virtual machine, and two failover clustering nodes

Business Intelligence Edition

          For the first time in the history of SQL Server, a Business Intelligence edition is offered. The Business Intelligence edition offers organizations the full suite of powerful BI capabilities such as scalable reporting and analytics, Power View, and PowerPivot. It is tailored toward organizations trying to achieve corporate business intelligence and self-service capabilities, but that do not require the full online transactional processing (OLTP) performance and scalability found in the Enterprise edition of SQL Server 2012.

Here is a high-level list of what the new Business Intelligence edition includes:

     Up to a maximum of 16 cores for the Database Engine

     Maximum number of cores for business intelligence processing

     All of the features found in the Standard edition

     Corporate business intelligence

     Reporting

     Analytics

     Multidimensional BI semantic model

     Self-service capabilities

     Alerting

     Power View

     PowerPivot for SharePoint Server

     Enterprise data management

     Data quality services

     Master data services

     In-memory tabular BI semantic model

     Basic high availability can be achieved with AlwaysOn 2-Node Failover Clustering

Above and beyond the three main editions discussed earlier, SQL Server 2012 continues to deliver specialized editions for organizations that have a unique set of requirements. Some examples include the following:

Developer Edition 

          The Developer edition includes all of the features and functionality found in the

Enterprise edition; however, it is meant strictly for the purpose of development, testing, and demonstration. Note that you can transition a SQL Server Developer installation directly into production by upgrading it to SQL Server 2012 Enterprise without reinstallation.

Web Edition 

Available at a much more affordable price than the Enterprise and Standard editions, SQL Server 2012 Web is focused on service providers hosting Internet-facing web services environments. Unlike the Express edition, this edition doesn’t have database size restrictions, it supports four processors, and supports up to 64 GB of memory. SQL Server 2012 Web does not offer the same premium features found in Enterprise and Standard editions, but it still remains the ideal platform for hosting websites and web applications.

Express Edition 

           This free edition is the best entry-level alternative for independent software

vendors, nonprofessional developers, and hobbyists building client applications. Individuals learning about databases or learning how to build client applications will find that this edition meets all their needs. This edition, in a nutshell, is limited to one processor and 1 GB of memory, and it can have a maximum database size of 10 GB. Also, Express is integrated with Microsoft Visual Studio.

* Simple select query to find the total index count in a database breakdown by type

As we all already know that index consideration in SQL Server mainly revolves around 2 types... Clustered and Non-clustered.

         The below query helps you to find out the actual index count in your database whether it is Clustered or Non-clustered.

It also provides you the count of tables without any indexes(Heaps)....

SELECT i.type_desc, count(*) as cnt FROM sys.indexes i  INNER JOIN sys.objects o  ON i.object_id = o.object_id  WHERE o.type<>'S' GROUP BY i.type_desc 

TOP 10 Features of SQL Server 2012

          The much awaited SQL Server 2012 is finally out in the market. Here below I am listing the most new and advanced features of SQL Server 2012 release which comes in handy for a quick reference.......

1. AlwaysOn Availability Groups -- The most important feature in the SQL Server 2012 release is the new AlwaysOn Availability Groups high availability technology. AlwaysOn Availability Groups is essentially the evolution of database mirroring. AlwaysOn can protect multiple databases. It supports up to four replicas and lets you mix and match both synchronous and asynchronous connections. In addition, unlike database mirroring, the data in the replicas can be actively queried.

2. Power View -- Power View (formerly code-named Crescent) is a graphical data navigation and visualization tool that enables end-user reporting. Power View provides a report designer that lets users take elements from a semantic data model that's constructed by IT and use them to create powerful interactive reports that can be embedded in .NET applications or published to SharePoint.

3. SQL Server Data Tools -- One of the most important developer-oriented features in SQL Server 2012 is the new SQL Server Data Tools development environment. SQL Server Data Tools uses the Visual Studio 2010 shell, and it enables model-driven database development as well as T-SQL and SQLCLR development and debugging. SQL Server Data Tools can connect to SQL Server 2005 and later as well as to SQL Azure.

4. Columnar index -- Primarily intended to support data warehousing, the columnar index feature incorporates the same high-performance/high-compression technology that Microsoft uses in PowerPivot into the SQL Server relational database engine. Columnar indexes store data column-wise, and only the necessary columns are returned as query results. Depending on the data, Microsoft states this technology can provide up to 10 times improvement in query performance with reduced I/O.

CREATE NONCLUSTERED COLUMNSTORE INDEX mycolumnstoreindex ON mytable (col1, col2, col3);

5. Contained databases -- Contained databases make it easy to deploy new databases and to move databases between different SQL Server instances. Users of a contained database don't need logins for the SQL Server instance. Instead, all authentications are stored in the contained database. Contained databases have no configuration dependencies on the instance of SQL Server that they're hosted on.

6. T-SQL enhancements -- SQL Server 2012 provides many T-SQL enhancements, including support for sequences, a new TRY_CONVERT operator for data conversions, OFFSET and FETCH for data paging, a new FORMAT() function for easier data formatting, a new THROW operator for enhanced error handling, and improved T-SQL windowing functions.

7. Data Quality Services -- Data Quality Services (DQS) is a knowledge-based tool that helps ensure your databases contain high-quality, correct data. DQS performs data cleansing, which can modify or remove incorrect data. It also does data matching to identify duplicate data and profiling that intelligently analyzes data from different sources. DQS is integrated with both SQL Server Integration Services and Master Data Services.

8. Support for Windows Server Core -- Windows Server Core is designed for infrastructure applications such as SQL Server that provide back-end services but don't need a GUI on the same server. The inability to run previous versions of SQL Server on Windows Server Core always seemed ironic. SQL Server 2012's support for Windows Server Core enables leaner and more efficient SQL Server installations and also reduces potential attack vectors and the need for patching.

9. Processor core licensing model -- With SQL Server 2012, Microsoft has moved away from counting sockets to a new core-based licensing model. The Enterprise edition can be licensed only per core. The list price is $6,874 per core. The Business Intelligence edition is licensed only per server; it goes for $8,592 per server. You must also purchase Client Access Licenses (CALs) per user. The CAL price has increased to $209 per CAL. The Standard edition has the option of being licensed either per core or per server; it costs $1,793 per core or $898 per server.

10. Simplified editions -- SQL Server 2012 will be delivered in three main editions: Enterprise, Business Intelligence, and Standard. The Enterprise edition contains all of the product's features. The Business Intelligence edition contains all of the BI capabilities but lacks some of the higher-end availability features. The Standard edition provides basic relational, BI, and availability capabilities. Microsoft has retired the Datacenter, Workgroup, and Standard for Small Business editions but will continue to provide the Developer, Express, and Compact Editions. The Web edition will be offered only to web hosting providers who have a Services Provider License Agreement (SLPA).

More new features to come.....Stay glued...........

* Oracle Error ORA-12154: TNS:could not resolve the connect identifier specified

      A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved

This is connection issue and the name, cannot be resolved...

Cause : 

        The type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.

Workaround : 

        This is a most common type of naming erros. Go through the below steps one by one and keep checking your connection meanwhile. Essentially you need not go through all the steps. Stop when connected......

1.       If you are using local naming (TNSNAMES.ORA file), make sure that "TNSNAMES" is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)

2.       Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.

3.       Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.

4.       Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.

5.       If you are using directory naming, Verify that "LDAP" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

6.       Verify that the LDAP directory server is up and that it is accessible.

7.       Verify that the net service name or database name used as the connect identifier is configured in the directory.

8.       Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier

9.       If you are using easy connect naming, Verify that "EZCONNECT" is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).

10.   Make sure the host, port and service name specified are correct.

11.   Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.

dbpioneers wishes you a happy resolution!!!!!!