Wednesday, April 9, 2014

Field simulator - SCADA - DMS - SQL Server

Today I have achieved very important goal - I have managed to setup almost live environment in VirtualMachine.
I have used xMaster as a field simulator. OASyS SCADA is set to accept the changes which are coming from the field (from xMaster). Schneider Electric DMS is connected to OASyS SCADA, and DMS is writing the changes which are coming from the field (and through all these components) to SQL Server.

Here is a picture of components:




Friday, April 4, 2014

BCP is also influenced by health of indexes and valid statistics

I have been running some bcp out statements, something like this:

bcp "select * from DatabaseName..TableName where Timestamp>='2013-12-23 00:00:00' and Timestamp<'2013-12-24 00:00:00'" queryout
C:\temp\TableName_2013-12-23.dat -S SQLServerMachineName\DatabaseInstanceName -T -n

I have noticed that for some days I am getting slower output, then for another days.

Since the TableName is partitioned by Timestamp, I did the index rebuild on partitions where "slow" dates exists. But it did not solve my problem. BCP was still slow.


Then I asked myself - why BCP is still slow? 

The answer came to my mind when I noticed that bcp is using SQL statement. Then I knew that the problem must be in that statement. Then I took that SQL statement, generate a query execution plan for a day when bcp is ok, and for a day when bcp is slow, and I noticed that they have different execution plans. 

The reason why they have the different execution plans is that statistics for indexes on TableName were wrong for the dates which have slow bcp. I thought that partitioned index rebuild will automatically update statistics, but it did not update it. So, I run update statistics manually, for all the indexes on that table.

After that, bcp was fast for all the days.

All maintenance actions on one place

I have manually started index rebuild on one system and noticed that it goes slower that I would expect. I issued sp_who2 to see what is happening and noticed that some other application is doing backup of the same database I am rebuilding the index. I got frustrated with that fact.
And how would I even know that this backup is scheduled, when this application has its own schedule which is not present in SQL Server, but in this application.
So, I have been thinking that all maintenance should be done in SQL Server. Or, that SQL Server is aware of that maintenance, and if someone is issuing something that SQL Server is not aware then SQL Server would, for example, forbid this action. So, maybe one way of protecting SQL Server (and other systems) would be that all the actions (all or some of them) should be pre-announced.