- ~ Posted by Deepak Kamboj on July 11, 2011
During the past few years there was a requirement from various industries like manufacturing, utilities, oil & gas and financial services for incorporating “events” and “event streams” into the applications. The demand emerged for a technology to be created where we can looked upon these applications as “Complex Event Processing (CEP) Applications” or “Low Latency Monitoring Applications”. These applications were referred to by different names in different domains ranging from manufacturing, utilities, oil & gas, financial services and IT monitoring to web analytics. Still, all these applications shared some common characteristics – the need to process large volumes of data in the form of event streams with minimal latency. In some applications the emphasis was on the ability to process events with very rich payloads (complex events) and in others the emphasis was on the ability to compute with minimum latency over very large event rates. Microsoft released the Microsoft® StreamInsight as Microsoft’s new Complex Event Processing technology to help businesses derive better insights by correlating event streams from multiple sources with near-zero latency.
Microsoft StreamInsight™ is a powerful platform that you can use to develop and deploy complex event processing (CEP) applications. Its high-throughput stream processing architecture and the Microsoft .NET Framework-based development platform enable you to quickly implement robust and highly efficient event processing applications. Event stream sources typically include data from manufacturing applications, financial trading applications, Web analytics, and operational analytics. By using StreamInsight, you can develop CEP applications that derive immediate business value from this raw data by reducing the cost of extracting, analyzing, and correlating the data; and by allowing you to monitor, manage, and mine the data for conditions, opportunities, and defects almost instantly.
By using StreamInsight to develop CEP applications, you can achieve the following tactical and strategic goals for your business:
-
Monitor your data from multiple sources for meaningful patterns, trends, exceptions, and opportunities.
Analyze and correlate data incrementally while the data is in-flight -- that is, without first storing it--yielding very low latency. Aggregate seemingly unrelated events from multiple sources and perform highly complex analyses over time.
-
Manage your business by performing low-latency analytics on the events and triggering response actions that are defined on your business key performance indicators (KPIs).
Respond quickly to areas of opportunity or threat by incorporating your KPI definitions into the logic of the CEP application, thereby improving operational efficiency and your ability to respond quickly to business opportunities.
-
Mine events for new business KPIs.
-
Move toward a predictive business model by mining historical data to continuously refine and improve your KPI definitions.
Source: http://msdn.microsoft.com/en-us/library/ee362541.aspx
- ~ Posted by Deepak Kamboj on August 17, 2010
Today we will focus on the best practices for SQL Server Indexing. These practices are known to us and is a recap which will assure optimal performance through indexes.
1) Guidelines on indexing decisions
- Keep the columns in an index to a minimum as wide indexes take longer to scan.
- Ensure a clustered index on every table. The clustered index should be on an unique column and used most frequently for retrieving data
- The column of the clustered index should not be frequently updated as every time a clustered index is updated, SQL Server also maintains the non-clustered index along with the clustered index for the non-clustered index contains a pointer to the clustered-index.
- Eliminate duplicate indexes i.e., multiple indexes on the same set of columns. This adds to extra storage space and has a performance penalty on the INSERT, UPDATE and DELETE statements.
- Creation of indexes primarily depends on frequently executed queries and the queries most important to the users. Index tuning wizard and Database Engine Tuning Advisor are built-in tools for analysing and recommending indexes and analysing the performance of SQL Server instances but the recommendation are relevant for the workload supplied to the tool.
2) Determine the fragmentation state of indexes
Fragmentation happens due to inserts, updates and deletes. Get the list of all indexes on the database where fragmentation is 15% or greater and index page count is greater than 50. Work out plan to rebuild the heavily fragmented indexes and reorganize the lesser fragmented indexes.
3) Ensure the data type of surrogate primary keys is correct.
The best example here would be a transaction table which has a surrogate key of int data type. As the records cross the 2147483647 limit, the DBA would realize that the data type should not be an int. Ensure the database is scalable.
4) Understand the following myths are not true:
- Rebuilding the clustered index would rebuild all the associated non-clustered indexes. This is true only if the REBUILD WITH DROP EXISTING option is specified.
- Reorganizing a clustered index causes all non-clustered indexes to be reorganized.
- A fill factor of 0% is not the same as a fill factor of 100%.
- ~ Posted by Deepak Kamboj on May 26, 2010
Today we will delve on how to disable triggers before a synchronization and how to renable them after the manual synchronization is complete.
Manual Synchronization takes place for online/offline applications wherein an offline/mobile version of database communicates with a second instance of the database that resides on the server and vice versa. For synchronization, there needs to be greater control on how the data is synchronized. In such a scenario, there would be triggers on various tables which lead to further insertions on other tables and these re-insertions are to be avoided. To avoid re-insertion, disable the trigger before synchronization and enable the trigger after the synchronization is complete.
The syntax to disable and enable trigger is given below:
DISABLE TRIGGER <TRIGGERNAME> ON <TABLENAME>
and
ENABLE TRIGGER <TRIGGERNAME> ON <TABLENAME>
Command syntax to disable all triggers of a table.
ALTER TABLE <TableName> DISABLE TRIGGER ALL
- ~ Posted by Deepak Kamboj on May 25, 2010
This is a pretty common situation that comes up when performing database operations. A stored procedure is called and the data needs to be updated if it already exists and inserted if it does not. If we refer to the Books Online documentation, it gives examples that are similar to:
IF EXISTS (SELECT * FROM Table1 WHERE Column1='SomeValue')
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
ELSE
INSERT INTO Table1 VALUES (...)
This approach does work, however it might not always be the best approach. This will do a table/index scan for both the SELECT statement and the UPDATE statement. In most standard approaches, the following statement will likely provide better performance. It will only perform one table/index scan instead of the two that are performed in the previous approach.
UPDATE Table1 SET (...) WHERE Column1='SomeValue'
IF @@ROWCOUNT=0
INSERT INTO Table1 VALUES (...)
The saved table/index scan can increase performance quite a bit as the number of rows in the targeted table grows.
Just remember, the examples in the MSDN documentation are usually the easiest way to implement something, not necessarily the best way. Also (as I re-learned recently), with any database operation, it is good to performance test the different approaches that you take. Sometimes the method that you think would be the worst might actually outperform the way that you think would be the better way.
Original Post: http://blogs.msdn.com/b/miah/archive/2008/02/17/sql-if-exists-update-else-insert.aspx
- ~ Posted by Deepak Kamboj on March 30, 2010
When trying to attach a database file created in Vista, my WinXP gave the following error:
"...is compressed but does not reside in a read-only database or filegroup"
This means that the file is compressed because when it was created (in Vista), the Vista disk is a compressed Disk. But when I transfer it to WinXP, the disk is not a compressed disk.
Solution:
On the WinXP (Uncompressed disk),
1. Right-click on the .mdf file
2. In the Context Menu that appears, select Properties
3. In the Properties Dialog that appears, click on the Advanced button
4. In the Advanced Attributes Dialog that appears, uncheck the Compress Contents To Save Disk Space checkbox. Then click OK. See image
5. Repeat the same thing for the .ldf file.
- Tags:
- Categories: SQL Server
- ~ Posted by Deepak Kamboj on September 4, 2009
If you see this post it means that BlogEngine.NET 1.5 is running with SQL Server and the DbBlogProvider is configured correctly.
Setup
If you are using the ASP.NET Membership provider, you are set to use existing users. If you are using the default BlogEngine.NET XML provider, it is time to setup some users. Find the sign-in link located either at the bottom or top of the page depending on your current theme and click it. Now enter "admin" in both the username and password fields and click the button. You will now see an admin menu appear. It has a link to the "Users" admin page. From there you can change the username and password.
Write permissions
Since you are using SQL to store your posts, most information is stored there. However, if you want to store attachments or images in the blog, you will want write permissions setup on the App_Data folder.
On the web
You can find BlogEngine.NET on the official website. Here you will find tutorials, documentation, tips and tricks and much more. The ongoing development of BlogEngine.NET can be followed at CodePlex where the daily builds will be published for anyone to download.
Good luck and happy writing.
The BlogEngine.NET team
- Tags:
- Categories: SQL Server