Thursday, September 26, 2013

[SQL 2012] Performance issues SQL 2012

[SQL 2012] Performance issues SQL 2012

Performance issues SQL 2012

Posted: 26 Sep 2013 12:22 AM PDT

Hi,Everyday I truncate a table of roughly 40 millions rows, populate it and re-create the indexes. If I restart the SQL server just before, the process take a bit more then 1 hour. After a week, the same process takes 2hours. I can't explain why the time of execution is increasing that much.I think this it the creation of the indexes that takes more time.Any idea where I can look at?Thanks

AlwaysOn performance on Replica databases

Posted: 26 Sep 2013 02:08 AM PDT

I am looking into AlwaysOn Availabilty Groups as both an HA and load spreading solution. But there is something I would like to clarify before I present to management. Our production databases suffer from lots of blocking and deadlocks. Will this resource contention be "replicated" on the replica databases when the logs are applied? I'd like to offload many of the read operations (adhoc reports, etc.) to the replicas but don't just want to end up in the same situation as I am in now.Thanks for any help.Tomp.s. - work is underway to address deadlocking, but it won't be fixed any time soon :(

Using cdc LSN for SSIS multiple table export without database snapshot

Posted: 26 Sep 2013 01:24 AM PDT

Hi,I have a range of tables enabled with CDC. At a set point I want to export all the net changes from these tables.I am using SSIS, and the first thing I need to do is decide on the start LSN and the end LSN.This is easy if I have a database snapshot. However, say that I do not.How do I ensure that I select the start LSN for each of the 5 tables so that the data exported fromall of these 5 tables reconcile back to each other like a snapshot in time?Do I record the time when the SSIS process is kicked off, and then use this time for each of the 5 table exports, to find the LSN? Would this remove any risk of the 5 tables being inconsistent?The start LSN is simpler since it will be the previous run's end LSN.Thanks

multiple conditions

Posted: 25 Sep 2013 03:59 AM PDT

hi, idk if I'll give to explain very well.but I need to make a query with the following conditionsexample:I have three fields are the parameters for my querythe fact is that when one of the parameters not leave blank or empty I need you always return the information and if not that if I put the three fields always return the information.thnk

SSRS Number - Custom Format Issue

Posted: 25 Sep 2013 09:17 PM PDT

Hi Please find the attached snapshot for SSRS Custom Number format issue.I am using as Number format :- #,##0.00%;(#,##0.00%)Font Format :- IIF(ReportItems!txtBox4.Value < 0 "Red" , "Black")But the result is coming with 0.00% as 3 type Black, Red(Without bracket) and Red(with bracket)Can you please let me know how #,##0.00%;(#,##0.00%) work ?

Query Issue

Posted: 25 Sep 2013 06:56 AM PDT

DECLARE @County NVARCHAR(100)DECLARE @Longitude decimal(19,15) = -87.979529DECLARE @Latitude decimal(19,15) = 42.219469DECLARE @SQLQuery AS NVARCHAR(500)DECLARE @LongitudeLatitude geographySET @SQLQuery = N'SET @LongitudeLatitude = geography::Parse(''POINT(' + cast(@Longitude as varchar) + ' ' + cast(@Latitude as varchar) + ')'')'-- SET @LongitudeLatitude = geography::Parse('POINT(-87.979529000000000 42.219469000000000)')print @SQLQuery exec sp_executesql @SQLQueryGives me Msg 137, Level 15, State 1, Line 1Must declare the scalar variable "@LongitudeLatitude".What could be the issue?

Replication Migration problem

Posted: 25 Sep 2013 07:36 AM PDT

I have a 2008R2 environment that I want to migrate to 2012 for replication. The production servers will remain 2008R2 and I want to replicate using a new 2012 distributor to a new 2012 destination. I have over 30 replication jobs to migrate and want to minimize downtime. My original thought was to add new publications to my existing production servers that would point to the new distribution database. From everything I have been able to find out, that is not an option. So, first question, does anyone know of a way to to this? This would be ideal for the new replication.Failing that, what is the best way to migrate (with least downtime and best possibility for rollback) from the old replication to the new. My original solution was to leave the old replication untouched until I could verify that the new replication was working properly. If I can't have a publisher that points at more than one distributor, then I don't see how I can do that.This seems like the only thing is to script out all the existing publications /subscriptions, modify them to point to the new servers with new logins etc. Drop all the old publications /subscriptions en masse and recreate them via the script pointing at the new environment. This seems like a fairly high risk maneuver, with little room for mistake or rollback.Any ideas? I'm sure I'm not the first to do this.Thanks,Dave Bennett

Best way to configure jobs on AlwaysON

Posted: 25 Sep 2013 04:34 AM PDT

Hi,We are planning to configure AlwaysON with 3 servers. Two in the same datacenter with synchronous commit and automatic failover and the third one in a different datacenter as a DR with asynchronous commit. Now what is the best way to configure the SQL jobs like Reindexing, DBCC Checkdb, Update Statistics and other application related jobs. The reindexing, update statistics and Checkdb should only be run on the Primary and should we keep them enabled on the secondary also.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog