Systems Engineering and RDBMS

Archive for September, 2008

Multi Server Queries in SQL Server 2008

Posted by decipherinfosys on September 30, 2008

SQL Server Management Studio (SSMS) in SQL Server 2008 version now has the capability to run the same command on multiple servers at the same time and the results are displayed to a single window.  This is nothing new from the perspective of third party tools that have been out there for quite some time but is something new in SSMS and is a welcome change for the DBAs/Database Developers since there are many times when we want to run the same command across instances in order to collect information or make changes.

The very first thing that one needs to do in order to make this possible is to create a server group.  On the SSMS Menu, click on View and then “Registered Servers”.  In my local server group, I have 2 instances registered:

Now, click on the server group and then click on “New Query” from the Menu and when the window opens up, type:

select  @@version

And the output will be shown in the results pane below for all the registered instances in that server group.  Here is the result output from my environment:

And as you can see from the output from above, this is backward compatible as well since I have one instance which is the SQL Server 2005 instance an the other one is a SQL Server 2008 instance.  Also, one instance can be part of more than one server group.

Posted in SQL Server | Leave a Comment »

My refresh materialized view is not refreshing – Part 1

Posted by decipherinfosys on September 29, 2008

We will be posting some blog posts for what to monitor and look for if your refresh materialized view is not refreshing as expected.  Part 1 i.e. this blog post will discuss checking the job scheduler for the materialized view refresh.

Refresh materialized views, for most, are something we don’t ever think about once implemented unless something goes wrong. One of the typical issues we may expect to see in a refresh materialized view is that the materialized view is not refreshing from the master table(s) or the refresh is just not able to keep up with the changes occurring on the master table(s).  The first question we should ask ourselves, is what is the general health of the materialized view.

We have several major parts to the materialized views:
1) The job that is scheduled to run the materialized view.
2) The materialized view table, also known as the mlog table.
3) The master table.  (AKA Source)
4) The materialized view.  (AKA Target)

In this post we are checking the scheduled job for refreshing the materialized view.  This is a good place to start, as it is at a high level and is easy to check and interpret.  There are a  few things we will want to check to see if the materialized view is in a healthy functioning state.  We will want to see when the last refresh was run, when it is scheduled to run next, and if the job is broken.  The below query will allow you to see all of this (written for 10gR2).  The query should be run on the location of the target as sysdba.

SELECT  /*+ RULE */
A.JOB JOB#,
SCHEMA_USER MVIEW_OWNER,
DECODE(SUBSTR(WHAT,INSTR(WHAT,’.’,1,2)+2,INSTR(WHAT,'”‘,1,4)-4-INSTR(WHAT,’.’,1,2)+2),NULL,SUBSTR(WHAT,1,40), SUBSTR(WHAT,INSTR(WHAT,’.’,1,2)+2,INSTR(WHAT,'”‘,1,4)-4-INSTR(WHAT,’.’,1,2)+2)) MVIEW_NAME,
LAST_DATE LAST_RUN_DATE,
NEXT_DATE NEXT_SCHED_RUN_DATE,
DECODE(BROKEN,’Y’,’YES’,’N’,’NO’,’ ‘) IS_BROKEN,
FAILURES,
RUNNING IS_RUNNING,
B.SID SID
FROM DBA_JOBS A
LEFT OUTER JOIN (SELECT /*+ RULE */
JOB,’YES’ RUNNING,SID
FROM DBA_JOBS_RUNNING ) B
ON A.JOB = B.JOB
ORDER BY SCHEMA_USER, MVIEW_NAME;

The real meat of the data are the LAST_RUN_DATE, NEXT_SCHED_RUN_DATE,  IS_BROKEN, and FAILURES.  These four columns will indicate if the job has been running, when the job will run next, if the job is in a working status, and how many failures (if any) that occurred since the last successful run, respectively.

The other columns in the query will help us to determine which materialized view we are looking at.  Some other information of interest is to see if the job is currently running, represented by IS_RUNNING and what is the SID value of the job.  Please keep in mind that these are the values from the target location, not the source location.

Tips on interpreting the data

If the LAST_RUN_DATE is from 5 hours ago and your refresh job is scheduled for every 15 minutes, this would indicate a lack in the ability for the materialized view to refresh itself.  The LAST_RUN_DATE does not get updated until the materialized view refresh has completed (i.e. until the job has completed). The NEXT_SCHED_RUN_DATE operates in the same fashion.  It will not update until the job is finished.

The IS_BROKEN column will indicate if the job is broken.  This is important, because if the job is broken it is not running.  If the job is not running, then the materialized view is not refreshing.

In the next post of this series, we will check the mlog table for the materialized view.  The mlog table is the table on the source that records the changes (from the master table) to be applied on the materialized view on the target.

Posted in Oracle | 1 Comment »

Differences between different SQL Server 2008 Editions

Posted by decipherinfosys on September 28, 2008

Choosing the right SQL Server 2008 Edition is something that requires a bit of a research. The first step is of course to become familiar with the available options. We have seen this question pop up a lot of times at client sites as well as in newsgroups and the e-mails that we get from the readers. Microsoft already has very good, concise and to the point information on the different editions, their capabilities as well as a comparison matrix between Enterprise & Standard Edition as well as a comparison matrix between Compact & Express Edition. There are a lot of choices available for you and depending upon your needs, you can choose from (all the details on these can be obtained from the link above):

  • Enterprise Edition
  • Standard Edition
  • Workgroup Edition
  • Web Edition
  • Compact Edition
  • Express Edition
    • SQL Server 2008 Express Edition.
    • SQL Server 2008 Express with Tools.
    • SQL Server 2008 Express with Advanced Tools.

A full comparison sheet for Enterprise vs Standard is available over here and a full comparison sheet for Compact vs Express is available over here.

UPDATE:

Thanks to Prashant for notifying us that the link that we had above points to an older document from MSFT which had the Compant and Express editions switched.  Here is the new link that he has provided:

http://www.microsoft.com/sqlserver/2008/en/us/compare-specialized.aspx

Thanks Prashant 🙂

Posted in SQL Server | 22 Comments »

First Cumulative Update for SQL Server 2008

Posted by decipherinfosys on September 28, 2008

First CU (Cumulative Update) for SQL Server 2008 has been released.  You can read more on it in the PSS blog over here:

http://blogs.msdn.com/psssql/archive/2008/09/26/welcome-to-sql-server-2008-patching.aspx

Posted in SQL Server | Leave a Comment »

Answers to some SQL Server questions

Posted by decipherinfosys on September 26, 2008

A friend recently asked some questions that we thought we would post over here and provide answers for those so that in case there are similar questions that you have, you can also benefit from those:

Q1) If you pass a parameter in a procedure, it will execute in a few seconds. If you pass a different set of parameters, it would take a longer time. Why and how this can be fixed.

A1) We have covered this before in our blog posts – this is called parameter sniffing in SQL Server and bind variable peeking in Oracle. There are multiple ways to fix it – plan guides, hints, usage of a constant instead of a parameter, using the optimize for clause. You can read more here:

https://decipherinfosys.wordpress.com/?s=parameter+sniffing

Q2) Why there are stored procedure recompilations ? When the query/stored procedure will recompile and generate a new plan?

A2) There are a lot of reasons for re-compilation. We have covered some of those in our posts before – too many to list here. But if you see this post, it also has the link to MSFT whitepaper which talks about all the different scenarios and resolutions:

https://decipherinfosys.wordpress.com/2008/02/12/set-options-and-execution-plans-ii/

Both the questions from above are answered in there. Also, remember that in SQL Server 2000, the unit of compilation was the whole stored procedure. One could not choose to recompile just one single query within the stored procedure. If one used the RECOMPILE option, the entire procedure got recompiled. SQL Server 2005 supports the statement level recompile – which is good because the other queries in the stored procedure can still use the cached execution plans rather than recompiling them again when there is no need to (and only a single query needs to be recompiled).

Q3) When we create a procedure what kind of query plan it generates?

A3) None. The plan does not get generated when we create the stored procedure. It gets generated when we execute it for the first time. Here is a test: Let’s create a stored procedure called dbo.usp_test. Prior to the run, let’s check in sys.syscacheobjects (there are other DMVs and DMFs as well that we can check but this will suffice for this post) … when we check in sys.syscacheobjects, of course there is nothing in there for usp_test since the object does not even exist right now. Let’s create it now:

create proc dbo.usp_test as select * from dbo.user_master
go

And now if we check the data again, we will still not see anything in there since we have just created the procedure. Now, let’s go ahead and execute the procedure and then check the data again. And this time, we will see a compiled plan with the objType as Proc. We can even check this using the query on the DMVs/DMFs:

SELECT
deqs.execution_count,
DB_NAME(deqp.dbid) as Database_Name,
OBJECT_NAME(deqp.objectid, deqp.dbid) as OBJNAME,
SUBSTRING (
dest.text,
(
deqs.statement_start_offset / 2
) + 1,
(
(
CASE deqs.statement_end_offset
WHEN -1 THEN DATALENGTH(dest.text)
ELSE deqs.statement_end_offset
END – deqs.statement_start_offset
) / 2
) + 1
) as Query_Text,
query_plan,
dest.text as Statement_Text,
total_elapsed_time
FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
CROSS APPLY sys.dm_exec_query_plan (deqs.plan_handle) deqp
WHERE OBJECT_NAME(deqp.objectid, deqp.dbid) = ‘usp_test’

And this will show the data which will show the execution count, the query text, the query plan etc..

Posted in SQL Server | Leave a Comment »

SQL Server Default Folder Locations

Posted by decipherinfosys on September 25, 2008

If you are configuring your backups in SQL Server using the GUI, you would notice that by default the backup folder location would be something like:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\

Likewise for the data and the log folders, the default location would be something like:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\

This is on my SQL 2005 default instance.

If you want to make changes to these default locations, these are stored in the registry.  However, since modifying the registry is not a good idea (sometimes we are forced to do it due to the limitations of the tools), good news is that for changing the default data and log file locations, we can do it through the GUI – right click on the instance name, select properties and go to Database Settings.  Over there, you will see Database Default locations at the bottom as shown in the image below:

However, if you want to change the location of the backup folder, you will not be able to find a place where you can make that change – you will need to use the registry for making that change.  On my default instance, this is available at:

HKLM\Software\Microsoft\Microsoft SQL Server\MSSQL.1\MSSQLServer

And under it, you will see Backup Directory as shown in the image below:

So, that is how you can go about changing the default locations for both the data/log file locations as well as the backup folder for a given instance.

Posted in SQL Server | Leave a Comment »

Google Phone Announcement

Posted by decipherinfosys on September 25, 2008

For those of you who are not on iPhone 3G yet and are with other carriers like T-Mobile, you will have an option now to go with the Google phone. T-Mobile/Google launched their new G1 phone running on the new android platform. Many people have blogged about this – one of the interesting posts in this regard is the one on gigaom that you can read here.

Posted in News | Leave a Comment »

MS Dynamics CRM v4: Many to Many Relationships Limitations

Posted by decipherinfosys on September 23, 2008

Even though Microsoft added M:N (Many to Many) relationship support to their CRM product, there are still a lot of issues with it. Some of the things that you need to keep in mind:

1) If you let MS Dynamics create the intersection table between the two custom entities which are in an M:N relationship, then there is no way for you to add any other attributes to that intersection table. This intersection table will have the GUID columns from the two parent tables and an additional GUID column for the intersection table itself.

2) The intersection table created by MS Dynamics does not support workflow. What this means is that one cannot fire off workflows when a record gets created that ties up those entities.

So, how can you get around those limitations? Well, a M:N relationship between two entities gets resolved by creating an intersection table. Rather than letting MS Dynamics create it for, you should create a table yourself and then define two N:1 relationships with the parent tables. That way, you can by pass both the issues that are mentioned above.

Posted in CRM | Leave a Comment »

Another Self Join scenario

Posted by decipherinfosys on September 22, 2008

In one of our earlier posts, we had covered self joins. Yesterday, while helping a client with their data processing logic a similar situation arose which was resolved by making use of a self join. The scenario is not an uncommon one in the healthcare industry. This private practice had prescriptions for their patients stored in the database (SQL Server lingo) and had the start and end dates for those prescriptions. They are required to track this and also furnish reports to the insurance companies as well as to the auditors and one of the things that they look for is whether the prescriptions overlapped or was their a gap in the prescription for the same medication etc.. One of the reasons why they want to see such reports also is to track malpractice – an example: We were told that some doctors were caught under the malpractice insurance law when they had un-necessarily prescribed pain killers to the patient and the patient had infact taken those and sold them in order to make a profit…

So, how does all this fit into a self join? 🙂 Well, the information is stored in the database tables and data is power, isn’t it? Especially when used correctly. So, let’s create some dummy data first:

create table patient_info (pat_nbr int identity not null primary key, pat_first_name nvarchar(10) not null, pat_ssn nvarchar(9) not null)
create table patient_presc (pat_presc_nbr int identity not null, pat_nbr int not null, presc_id int not null, start_date_time datetime not null, end_date_time datetime not null,
constraint pk_pat_presc primary key (pat_presc_nbr),
constraint fk_to_pat_info foreign key (pat_nbr) references patient_info (pat_nbr))
create unique index patient_presc_ind_1 on patient_presc (pat_nbr, presc_id, start_date_time);

go
set nocount on
go
insert into patient_info (pat_first_name, pat_ssn) values (‘tom’, ‘123456789’);
insert into patient_info (pat_first_name, pat_ssn) values (‘harry’, ‘012345678’);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’03/01/2008′, ’03/31/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’03/20/2008′, ’04/15/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 2, ’03/22/2008′, ’04/20/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’05/01/2008′, ’05/30/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’07/01/2008′, ’07/31/2008′);
insert into patient_presc (pat_nbr, presc_id, start_date_time, end_date_time) values (1, 1, ’07/25/2008′, ’08/25/2008′);

If you look into the data, you will see that there are two scenarios where there is an overlap of the prescriptions – for the same prescription (same medication), before the previous prescription’s date ended, a new prescription was issued. This is a cause for an audit which will then be followed up by the auditor with the doctors and nurses in the practice – there always is a reason code that is also stored in the tables as well as the doctor/nurse information but for the sake of this example, we have not shown that piece of information. We can get this information by doing a simple self join – here is the SQL:

select ‘Overlapped Prescriptions’,
pi.pat_first_name, pp1.end_date_time, min(pp2.start_date_time) as next_presc_start_date
from dbo.patient_info as pi
inner join dbo.patient_presc as pp1
on pi.pat_nbr = pp1.pat_nbr
inner join dbo.patient_presc as pp2
on pi.pat_nbr = pp2.pat_nbr
where pi.pat_ssn = ‘123456789’
and pp1.presc_id = pp2.presc_id
and pp1.start_date_time < pp2.start_date_time
and pp1.end_date_time < pp2.end_date_time
group by pi.pat_first_name, pp1.end_date_time
having datediff (dd, pp1.end_date_time, min(pp2.start_date_time)) < 1

                         pat_first_name end_date_time           next_presc_start_date
------------------------ -------------- ----------------------- -----------------------
Overlapped Prescriptions tom            2008-03-31 00:00:00.000 2008-03-20 00:00:00.000
Overlapped Prescriptions tom            2008-07-31 00:00:00.000 2008-07-25 00:00:00.000

Another thing that you would note is that there is 1 scenario where there is a gap of more than 30 days between the same type of prescription to the same patient. This could be patient negligence or could even be a valid scenario where the same disease (maybe a viral infection) occurred at different times leading to the same prescription being given again within 45 days but after a gap of 30 days. We can get this information by using the same SQL as above but changing the having criteria a bit:

select ‘Prescription Gap of more than 30 days and less than 45 days’,
pi.pat_first_name, pp1.end_date_time, min(pp2.start_date_time) as next_presc_start_date
from dbo.patient_info as pi
inner join dbo.patient_presc as pp1
on pi.pat_nbr = pp1.pat_nbr
inner join dbo.patient_presc as pp2
on pi.pat_nbr = pp2.pat_nbr
where pi.pat_ssn = ‘123456789’
and pp1.presc_id = pp2.presc_id
and pp1.start_date_time < pp2.start_date_time
and pp1.end_date_time < pp2.end_date_time
group by pi.pat_first_name, pp1.end_date_time
having datediff (dd, pp1.end_date_time, min(pp2.start_date_time)) > 30
and datediff (dd, pp1.end_date_time, min(pp2.start_date_time)) < 45

                                                            pat_first_name end_date_time           next_presc_start_date
----------------------------------------------------------- -------------- ----------------------- -----------------------
Prescription Gap of more than 30 days and less than 45 days tom            2008-05-30 00:00:00.000 2008-07-01 00:00:00.000

Posted in DB2 LUW, Oracle, SQL Server | Leave a Comment »

Microsoft Dynamics CRM links

Posted by decipherinfosys on September 19, 2008

As you dig more into MS Dynamics CRM, here are a list of some useful sites and blogs which you might find useful for your work:

http://www.decipherinfosys.com/MS_Dynamics_CRM.opml

Posted in CRM | Leave a Comment »