SQL SERVER – SSMS: Scheduler Health Report

Have you ever been to a music concert? It is the most humbling experience one can get as a music lover. The live music with hundreds of musicians in one stage brings goosebumps with the very thought. I have always been fascinated and wanted to experience this once in a lifetime and am sure that day is not far off. What strikes me big time is the conductor, standing alone with a small stick orchestrates these fabulous musicians to give all of us a delight and experience of a lifetime. This brings me to today’s topic of discussion on the Scheduler Health Report. In a way the conductor inside SQL Server is our scheduler – who makes sure all the activities and all parts get their share of time to execute. Looks like a dream job, but trust me there is lot of effort in understanding how each components works just like how a conductor really knows when to introduce a new instrument as part of the concert. Before I start explaining about this report component, it’s important to go through the basics of SQL Scheduler which would help in understanding this report.

SQL Server has a mini operating system which manages the resource by its own, that’s why you would hear term called SQLOS. By resources we mean CPU, Memory and IO available on the machine.

Whenever any request is received in SQL Server, it would be assigned to a thread and that thread would be scheduled on a scheduler. This might go to multiple scheduler in case of parallelism though. Those threads which are ready to run would be scheduled and sent to the operating system for execution. Imagine a situation of blocking where a blocked thread can’t do anything unless the resource is available. In such situation, does it make sense to send this request out to the operating system for scheduling? Of course not! That’s why this mini operating system does better scheduling and SQL can scale up very well as workload increases. Another advantage of the SQLOS layer is to reduce context switching of threads in operating system because it only sends the threads which can do some meaningful work.

To summarize, SQLOS is a mini operating system within sqlservr.exe process which takes care of managing CPU, Memory, locks, IO and a lot more. In general terms, the scheduler is a logical CPU on SQL Server side. In general, each scheduler is mapped to one logical processors exposed by the operating system. There are hidden and visible scheduler in SQL Server. They can be looked into via DMV sys.dm_os_schedulers

To know more about our Scheduler, here is the place to get this report. Right Click on Server node, choose Reports > Standard Reports > Scheduler Health.

The complete reports revolves around SQLOS. SQLOS has something called non-preemptive scheduling (also known as cooperative scheduling) which is different from the scheduling done by the operating system. Windows operating system does preemptive scheduling where a thread would get a fixed amount of time to run on the CPU. Once the time slice is completed, it would be snatched out of the CPU and put into the queue for the next chance to run. This is a fair game because all threads are getting a chance to run. On the other hand, in SQLOS a thread would do its work and come back to the scheduler by its own; no one is going to take him off the scheduler. This term is called as yielding. If a thread went out from SQLOS layer and didn’t come back – it’s called as non-yielding situation. If all schedulers have the same problem of non-yielding them you can imagine that SQL would go to “hung” state. A scheduler can be in three states – Idle (when work_queue_count <> 0), Hung (when yield_count is not changing) or Active (when it’s not in the other two states). Hence the first part of our report shows which states our Scheduler currently is in. In our case, the scheduler is in the Idle state.

The second part of the report shows the details about worker (can also be accessed via DMV sys.dm_os_workers), tasks (accessed via DMV sys.dm_os_tasks) and processes running under each scheduler. Let us understand these terms in the little details as it would help you in understanding this section of report better.

Task – represents the work that needs to be performed. It can also be called as unit of work that is scheduled by SQL Server. An example of task could be pre-login, login, query execution, logout and may more. The task can be in various states (PENDING, RUNNABLE, RUNNING, SUSPENDED, DONE or SPINLOOP). Please refer this for more details.

Worker - are the threads who would do the task given by the scheduler.
Request – is the logical representation of a request made from the client application (or work done by system threads) to SQL Server. This request would be assigned to a task that the scheduler hands off to a worker to process.

Now that our fundamentals have been sorted, let us have a look at the second report section:

My machine has currently has 8 logical processors and hence we are seeing values of Scheduler ID’s: 0 to 7. Other schedulers are having status as “HIDDEN ONLINE” in sys.dm_os_schedulers. Each scheduler has various workers associated. We can see that in column #Workers for each scheduler rows. Once we click on (+) for the scheduler, we can see details about each worker. Further clicking on (+) for each worker, we can see the work done by that worker.

I hope that this blog has helped you in understanding the basic functionality of SQLOS and how the Scheduler Report drills into the fine prints.

Reference: Pinal Dave (http://blog.SQLAuthority.com)

About these ads

Developer’s Life – Every Developer is a Harry Potter

Harry Potter might not be a superhero in the traditional sense, but I believe he still has a lot to teach us and show us about life as a developer.  If you have been living under a rock for the last 17 years, you might not know that Harry Potter is the main character in an extremely popular series of books and movies documenting the education and tribulation of a young wizard (and his friends).

Throughout the seven books and eight movies, Harry Potter learns to face his fears, trust his friends, and grows as a person.  This series was so beloved because of the real life lessons each story contained.  So what can we learn about developers from the world’s most famous wizard?

So how are developers like Harry Potter?

Well, read on my list of reasons.

Never Give Up

Never give up.  Harry Potter faced some astounding obstacles in his life – first, losting his parents, living with his horrible aunt and uncle, and facing He-Who-Shall-Not-Be-Named, the most evil wizard in all history.  Developers may not face pure evil brought back to human form, but they certainly do not give up in the face of difficult problems.

Have Dependable Friends

Rely on your friends.  Throughout the books, Harry learns that he cannot stand up to his troubles alone, and that he has to trust his friends to be by his side.  Developers may not have gone to wizarding school with two of their closest friends, but they know that teamwork is extremely important to job performance.

Fake it until You Make it

Fake it until you make it.  Harry Potter was just eleven when he faced his first challenge in Harry Potter and the Philosopher’s Stone.  Think back to what you were doing when you were eleven years old – certainly not battling an evil wizard!  Developers often face challenges they have never heard or seen before, but that doesn’t mean they give up.

Go with Faith

We choose “good” every day.  In Harry Potter and the Philosopher’s Stone, the students are sorted into “houses” by a magical hat.  When it is Harry’s turn, he hopes with his whole heart to be sorted into Gryffindor, not Slytherin.  Throughout the series, this choice makes Harry question his own motives – is he good in his soul, or good by choice?  In the end, he learns that we all have to choose “good,” and wanting to choose, it is as good as being good in your soul.  Programming problems may not be the choice between good and evil, but developers often have to choose between a quick patch and a permanent solution.  Wanting to do a good job and developing a permanent solution may not be easy, but wanting to accomplish it is half the battle.

Reference: Pinal Dave (http://blog.sqlauthority.com)

MySQL – How INSERT() Function Works for MySQL

You know there is an INSERT statement which is used to add data to a table. But did you know that there is an INSERT function which will replace certain characters within a string?

INSERT() function in MySQL is used to insert a substring at the specified position up to the specified number of characters.

Let us consider this example.

set @str:='This is nothing';

The variable @str has a string value “This is nothing”. Suppose you want to change “nothing” into “everything” in this string. You can use INSERT() function as shown below

select INSERT(@str,9,2,'every');

The INSERT() function replaces the characters starting from the position 9 to next 2 characters by “everything”. So in the string part “nothing”, no is replaced by “every” and the full string becomes “This is everything” .

You can also add a string between two strings as shown below

set @str:='This is nothing';
 select INSERT(@str,9,0,'not ');

The result is “This is not nothing”. When the third parameter is 0, it will not replace any characters, but append it at the starting position defined in the second parameter

Note: This INSERT() function is functionally equivalent to SQL Server’s STUFF() function. You can refer the usage of STUFF() function in this post.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – What is Biml and How Can it Help Me with SQL Server BI? – Notes from the Field #036

[Note from Pinal]: This is a 36th episode of Notes from the Field series. One of the common question I receive every other day is – I have learned BI but I feel that there is no automation in the BI field, what should I do? How do I go beyond learning BI? How can I fill the gap between BIDS and SSDT? If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains what is Biml and How Can it Help Me with SQL Server BI?. Read the experience of Reeves in his own words.


With all of the new technologies to learn and implement, I wanted to introduce you to some of the benefits of spending the time to learn Business Intelligence Markup Language (Biml). You can use this XML-based language to create and automate Microsoft SQL Server Business Intelligence (BI) objects. Biml currently supports BI objects like SSIS packages and SSAS dimensions, measures, and cubes.

After a quick overview of Biml, I’ll demonstrate how to use it by providing a walkthrough example.

Overview

You can use Biml to create tables, flat files, SSIS packages, and SSAS models. This language is human readable, unlike the XML that is represented within dtsx files and SSAS objects. This readability can help reduce the learning curve required for most programming languages.

Biml will not teach you SQL Server Integration Services (SSIS) or SQL Server Analysis Services (SSAS) but can enhance and increase your productivity with these tools with certain use cases. Biml is not the silver bullet that can solve all of the SSIS/SSAS development project problems, but I will discuss some of the scenarios where Biml excels.

The Biml languages is supported within two Integrated Development Environments (IDEs): Business Intelligence Development Studio (BIDS) and SQL Server Data Tools (SSDT), with the BIDS Helper add-in (free) or the Mist application from Varigence (purchased product).  Currently BIDS Helper is supported with SQL Server 2005, 2008, and 2008 R2 BIDS, and SQL Server 2012 SQL Server Data Tools. This article will focus on the BIDS Helper add-in and its capabilities with SSIS.

Simple Walk Through

This walkthrough will show how to create a Biml file that creates an SSIS package to move data from the AdventureWorks database to a staging database. The following script file will create all of the needed objects. If you have the AdventureWorks database installed and a table called HumanResources.Department, you can skip Listing 1.

CREATE DATABASE [AdventureWorks]
GO
USE [AdventureWorks]
GO
CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]
GO
CREATE TABLE [HumanResources].[Department]
(
[DepartmentID] [smallint] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[GroupName] [nvarchar](50) NOT NULL,
[ModifiedDate] [datetime] DEFAULT (GETDATE()) NOT NULL
)
ON [PRIMARY]
GO
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Engineering',N'Research and Development')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Tool Design',N'Research and Development')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Sales',N'Sales and Marketing')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Marketing',N'Sales and Marketing')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Purchasing',N'Inventory Management')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Research and Development',N'Research and Development')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Production',N'Manufacturing')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Production Control',N'Manufacturing')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Human Resources',N'Executive General and Administration')
INSERT INTO [HumanResources].[Department] ([Name], [GroupName]) VALUES (N'Finance',N'Executive General and Administration')
GO

asdfas
Listing 1 – AdventureWorks Database Objects

CREATE DATABASE [AdventureWorksStage]
GO
USE [AdventureWorksStage]
GO
CREATE SCHEMA [HumanResources] AUTHORIZATION [dbo]
GO
CREATE TABLE [HumanResources].[Department]
(
[DepartmentID] [smallint] PRIMARY KEY NOT NULL,
[Name] [nvarchar](50) NOT NULL,
[GroupName] [nvarchar](50) NOT NULL,
[ModifiedDate] [datetime] NOT NULL
)
ON [PRIMARY]
GO

Listing 2 – AdventureWorksStage Database Objects

To check that you have BIDS Helper installed within your Visual Studio environment, select the Tools menu option and select Options.The BIDS Helper option will display on the left dropdown window if it is installed, as you can see in Figure 1. If you need help installing the BIDS Helper add-in see: Step 2 in the Stairway to Biml: Biml Basics – Level 2 (link).

Figure 1 – Options windows with BIDS Helper installed

After verifying the BIDS Helper is installed, open a new SSIS Project from the File menu, and select New, then Project as Figure 2 shows.

Figure 2 – New Integration Services Project

Due to a Visual Studio limitation, right clicking on the Miscellaneous folder will not bring up the Biml context menu, which you see in Figure 3. Instead, from the Project Window, select the Project folder or the SSIS Packages folder, right click and select Add New Biml File. A new Biml file (BimlScript.biml) is added to the Miscellaneous folder.

Figure 3 – Biml Context Menu

Replace all of the code within the Biml File with the code from Listing 3.

<Biml
 xmlns="http://schemas.varigence.com/biml.xsd">
 <Connections>
 <OleDbConnection Name="AdventureWorks" ConnectionString="Provider=SQLNCLI10;Integrated Security=SSPI;Initial Catalog=AdventureWorks;Data Source=localhost;" />
 <OleDbConnection Name="AdventureWorksStage" ConnectionString="Provider=SQLNCLI10;Integrated Security=SSPI;Initial Catalog=AdventureWorksStage;Data Source=localhost;" />
 </Connections>
 <Packages>
 <Package Name="HumanResources_Department_Biml" ConstraintMode="Linear" >
 <Tasks>
 <Dataflow Name="Data Flow Task">
 <Transformations>
 <OleDbSource Name="OLE DB Source" ConnectionName="AdventureWorks">
 <ExternalTableInput Table="[HumanResources].[Department]" />
 </OleDbSource>
 <OleDbDestination Name="OLE DB Destination" ConnectionName="AdventureWorksStage">
 <InputPath OutputPathName="OLE DB Source.Output" />
 <ExternalTableOutput Table="[HumanResources].[Department]" />
 </OleDbDestination>
 </Transformations>
 </Dataflow>
 </Tasks>
 </Package>
 </Packages>
</Biml>

Listing 3 – Sample Biml code

Note: The connection information in the Biml file must point to the SQL Server instances that contain the AdventureWorks and AdventureWorksStage databases. If you are using another version of SQL Server the provider information might need to be changed: Provider=SQLNCLI10;

After replacing the code in the Biml file with the code in Listing 3, right click the file and choose the Check Biml for Errors menu option shown in Figure 4.

Figure 4 – Check Biml for Errors

If everything is configured correctly, you will receive the message shown in See Figure 5.

Figure 5 – No errors

If you receive any errors, you will need to correct them before selecting the Generate SSIS Package menu option.

Select the Generate SSIS Package menu option to create a new integration services package. A working package will be created within the Packages folder named: HumanResources_Department_Bimlas defined within the Package Name parameter within the Biml file.

Additional Walk-Through Examples

To add an Execute SQL task to truncate the stage table prior to the load add the following code after the <Tasks> node and before the <DataflowName=”Data Flow Task”>

<ExecuteSQLName="Truncate Table"ConnectionName="AdventureWorksStage">
 <DirectInput>TRUNCATE TABLE [HumanResources].[Department]</DirectInput>
</ExecuteSQL>

At this point in the walk-through Biml has not saved you much effort, but what if you wanted to create additional SSIS packages for all for the HumanResources tables within the Adventure works database? How much development effort would it take to update the Biml file to point to the each of the tables in the HumanResources schema and select Generate SSIS Package? Would those packages be consistent with the packages built prior? Would the prior testing of the previous package have a lot of benefit to the newly created package?

At this point copy and paste the Biml code within a new Biml file and move data from other tables. All of the table objects will need to exist, so you might have to add some staging tables to your stage database.

Practical Use

The first question that comes up after starting a discussion on Biml is, “Why would I us Biml?” I can create all of the SSIS packages I need in the current IDE.

One use case for Biml is the automation of similar design patterns like adding more packages to the walkthrough above. SSIS packages typically move data from a source to a destination. This pattern is repeated for each source and destination and can produce multiple SSIS packages. With some SSIS design patterns, the only items that change are the source table name and the destination table name.  What would it be like to spend the time to properly design and test a package to move data from one table to another and then duplicate that package with ease and only change the relevant information?  Biml enables this type of development.  With Biml, you are able to focus the development effort on design and remove some of the repetitive work.

As the variation of the packages increases between each package, Biml can become a less viable option. With Biml Script (a scripting language within Biml), you are able to create designs that vary and can adapt to changing Extract-Transform-Load (ETL) requirements.  Biml Script enables you to programmatically change items within the file without using cut and paste. (Biml Script was not demonstrated within this article.)

Looking Ahead

Biml is much more robust than what was demonstrated in this article but I wanted to start the discussion with a simple example to get you familiar with Biml. Adding Biml Script can create programmatic solutions that can automate SSIS package development. In a future article I will demonstrate how to add Biml Script to the existing code to increase package automation.

To enable follow along with the upcoming articles, install the AdventureWorks database from the following URL:. This database will also give you additional options to test and experiment with.

Side Note: The Mist IDE is able to extend Biml even further by updating multiple SSIS packages. This enables a maintenance option that has never been available within the SSIS development environment. It’s really cool, but something I will have to discuss in the future article

Stay Tuned.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQLAuthority News – Two Whitepapers on Performance Tuning and Enhancements

The best part of being in the technology domain is that I get a lot of opportunity to talk and write at different places. I travel quite a bit in lengths to deliver some of my learnings with audiences around the world. These learnings and writings as in this blog have influenced and encouraged a number of others to also jump into the blogging or writing habit. I cherish and love doing these outside of this site too. Recently, I wrote a couple of whitepapers and thought it was worth a call here over this blog too. What are these whitepapers all about?

Beginning Performance Tuning

If you want to learn a new competency, where would you start? One cannot wake up on a warm sunny day and start driving on their own. There is a process, steps and most importantly a learning part from an expert to get them started. And over a period of time this practice will make them expert too. The analogy hold good even here. There is no silver bullet to performance tuning and there is no “it depends” to performance tuning.

The facts and the process to learn is what gets discussed in this whitepaper. The best person to start performance testing are the developers and DBAs of the application itself. They exactly know the architecture, workload, dependencies and how access happens in the system. In this whitepaper I talk about this fundamental process first and then delve into some of the simplest techniques I have used for ages to do performance testing.

You can read the Whitepaper: Beginning Performance Tuning with SQL Server 2014 here.

Performance Enhancements with SQL Server 2014

Second in series that I wrote was around Performance Enhancements with SQL Server 2014. The need to upgrade to the latest stack always has its own challenges and administrators are constantly looking at the need to understand the new features so that it can help them at work. This constant struggle and need to learn, implement and troubleshoot the latest and greatest is a challenge in itself.

In this whitepaper, I take my random 4 picks of performance enhancements with SQL Server 2014 which I personally felt are worth a mention. Some of them include ColumnStore Index, Managed Lock Priority, Buffer Pool Extensions and more. I take an introduction to each of these enhancements and what these features bring to the table for an administrator / developer. In reality, each of these topics warrant a whole whitepaper but I am sure the future blogs will contain some of these for sure. Stay tuned on that.

You can read the Whitepaper: Performance Enhancements made with SQL Server 2014 here.

I am sure you will enjoy these whitepapers and the best part is they are absolutely FREE. So do download them and let me know your thoughts. Would love to incorporate some of them in my future writing on this blog or papers I write.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Round Up From Notes from the Field of Blog Posts of Tim Radney

We have completed 35 episodes of the series Notes from the Fields. I have been blogging for over 8 years and I have blogged about pretty much everything SQL and lots of other concepts. Though, I have extensive experience with SQL and Databases, it is always a good idea that we consult experts for their advice and opinion. Following the same thought process, I have started this new series of Notes from the Fields. In this series, we have notes from various experts in the database world.

My friends at Linchpin People have graciously decided to support me in my new initiation.  Linchpin People are database coaches and wellness experts for a data driven world. This series has been excellent From the very first episode of the Notes from the Fields series, we have received tremendous response. We are also very fortunate that database expert Tim Radney (partner at Linchpin People) has shared many insights about a very daily issue DBA and Developers face in their career. By the way, Congratulations to Tim for Microsoft MVP award.

Tim has been amazing in supporting this series. Here is the time when we can help out Tim. Tim would like to know which of his Notes from the Field is your most favorite Tim. 

Gift from Pinal

Here is a small contest. I have my favorite blog posts from following list. If your favorite post and my favorite choice are the same – I will give you USD 50 Amazon Card.

Remember, you need to tell me your favorite post and the reason why you liked it! The contest ends at Midnight GMT on July 16.

Please leave your answer in comment area.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Documentation Tool

This article explains how to use ApexSQL Doc, a SQL documentation tool. You can download ApexSQL Doc, and follow along with this article.

ApexSQL Doc is a SQL Server database development tool, for generating database and SSIS package documentation. It generates documentation in various output formats such as chm, html, doc, and docx. You can include relationship diagrams in a documenting process and customize the preview settings. ApexSQL Doc shows extended properties for all database objects created at the SQL Server level. Using the Extended property editor feature you can manipulate with previously created description or add a new one.

You can choose database objects to include in the documentation, along with the appropriate attributes. Documentation process can be scheduled and automated using the Command Line Interface (CLI). Available output styles can be applied to the output documentation file, or edited additionally.

A new version of ApexSQL Doc 2014 supports SQL Server 2014 and Integration Services 2014. It has a new UI style. Starting from ApexSQL Doc 2014 R2, Windows Azure will be supported, and PDF will be added as a new output format.

Starting the application will open main window where you can start a new project. Clicking the New buttonallows you to add an SQL Server instance, or Integration Services package. You can add multiple SQL Servers as well. To add a SQL Server instance or SSIS package, click the appropriate option marked in the image below:

SQL database documenting

If you choose to connect to SQL Server, the Add SQL Server dialog opens. You can specify local server, or type the address for a remote one, and choose the authentication method. In the Connection options dialog you can increase the values for the Connection timeout and Execution timeout options if you have a slow connection with a remote server.

When a SQL Server is connected, a list of the databases will be shown in the Databases section, where you can select the databases to include in the processing. Check the box for the database you want to add, or select multiple databases from a multiple SQL Servers, to document them at once:

ApexSQL Doc allows you to document server objects. For each combination of the selected server objects, the bitwise set number is generated, and can be used in the CLI, as this number represents a specific object combination, so you don’t have to include the objects manually. For the highlighted object type, a list of the object instances is shown in the object filter section, where you can select a particular instance, and include it in the documenting process. The selected combination of the server objects can be saved as an external file, clicking the Export button, and used later in another project:

In the Database options section, you can set additional options related to database documentation. ApexSQL Doc allows you to document system objects, DDL scripts, and highlight SQL syntax in the output document. In this section, you can set the object grouping (by schema or filegroup), sort objects by the owner/schema section, or include the extended properties. In the Table of Contents section, you can include a Dictionary section for a parameter/column names. Empty sections, or nodes with no relevant metadata existing can be included/excluded from the documentation. Including system objects, DDL scripts, or T-SQL highlighting can slow down the process, as this is performance intensive options:

The next step is to select the database objects to be documented. Checking the box for the appropriate object will include it in the documentation and at the same time will show the list of the object attributes, which you can choose to document. The same as for server objects, the bitwise set number is generated for each combination of the selected database objects, and can be used in the CLI, or the selection can be saved as an external file, and used later in another project:

Server and database objects can be filtered using the Filter editor feature. To enable filtering for the specific object, select the appropriate Use filter checkbox, and the list of the objects will come up under the Object filter tab. Here you can choose the specific objects to document, selecting the related checkbox.

For advanced filtering, click the “…” button, and the Filter editor dialog will open. Here you can specify the filtering conditions to apply to a selected object. Depending on the selected criteria, the objects that satisfy the logical conditions will be included or excluded from the documenting:

ApexSQL Doc allows you to include graphical dependencies in the documentation, and to customize the dependency type and layout settings. Documenting graphical dependencies requires more hardware resources, and can slow down the documenting process. Changing the value of the Scale option will increase/decrease a scale of the graphical dependencies in the output document. This will allow you to improve a visibility of the graphical dependencies in the output document. Object dependencies can be presented as tables, checking the Include dependency list/tables option:

Under the Styles tab, you can select the style to apply to the output document. Choose one of the predefined styles, or edit any of them manually to suit your needs. For a full preview of the output styles, the JavaScript option needs to be enabled for the current Internet zone. Any changes you made will be shown in the Preview section:

Before you generate the documentation, you can edit database extended properties using the Extended property editor. ApexSQL Doc allows you to review any object description added at the SQL Server level, or to add a new description. To add a description click the “…” button, and the popup window will open. Adding the description in the Extended property editor saves it automatically in the database.

Once you have everything set, you can generate the documentation, clicking the Generate button from the Home tab:

SSIS package documenting

ApexSQL Doc allows you to document online (from SQL Server, or Integration Services) and offline (from the file system) SSIS packages. To add a package, under the Integration services tab, click the Add button. When the dialog opens, choose the appropriate package source. New version of ApexSQL Doc supports SSIS 2014. You can include multiple packages from multiple sources, and document them at once:

When the SSIS packages are added, from the Package details tab choose the objects you want to document. You can include package source (*.dtsx) file as well:

All output options (format, style) apply to SSIS package documenting as for the databases. When the SSIS packages documenting is set, click the Generate documentation button from the Home tab.

ApexSQL Doc allows you to automate the documentation process, using the Command Line Interface, and schedule it using SQL Server Job, or Windows Task Scheduler.

Output files

Using the Save as option in MS Word, you can save the documentation file as a PDF file. Currently available output formats are chm, html, doc, docx (pdf through docx).

Here are the examples of the generated documentation:

Reference: Pinal Dave (http://blog.sqlauthority.com)