Thursday, June 13, 2013

[MS SQL Server] Fragmented pages in tables not changing

[MS SQL Server] Fragmented pages in tables not changing

Fragmented pages in tables not changing

Posted: 13 Jun 2013 05:24 AM PDT

I have several tables in a database that shows a high percentage in fragmentation. I created a maintenance plan and runs succesfully however, the fragmentation is not changing. For example the table below has a 52% fragmentation and pagecount of 109,208 but the rebuild index task doesn't change it.objectname objectid indexid partitionnum frag pagecountpricevalue 832955731 1 1 52.0331600907694 109288I'm just wondering if there's anything I need to do or that I'm doing wrong.Thank you.

SSIS Job - Intermittent Login Timeout Failures

Posted: 30 Nov 2010 11:48 PM PST

I have been running into some weird problem - I have SSIS jobs scheduled to run on a regular basis. Most of the time they run without any problem, but lately I got more and more sporadic job failures like the following message. The failure does not persist for long as the same job will run fine later without any intervention.Everything else on the server seems fine. I don't see any info in the sql error log. Any ideas? Thanks!------------Microsoft (R) SQL Server Execute Package Utility Version 10.0.2531.0 for 64-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 9:23:26 AM Could not load package "xxxxxxx" because of error 0xC0014062. Description: The LoadFromSQLServer method has encountered OLE DB error code 0x80004005 (Login timeout expired). The SQL statement that was issued has failed. Source: Started: 9:23:26 AM Finished: 9:23:42 AM Elapsed: 15.039 seconds. The package could not be loaded. The step failed.

Importing SSIS Package fails with Invalid characters

Posted: 30 Aug 2011 07:37 AM PDT

I've recently installed SQL Server 2008R2 with SP2 and SSIS. When we try import SSIS packages from SQL 2005 we get the following error: "Object name "䎹녞숋㤧 " is not valid. Name must contain only alphanumeric characters or underscores "_".Nothing unusual about the install, collation of SQL is Latin1_General_CI_AS.Package is going to the File System, Package name is Package1 and protection level is "Keep protection level of the original package"Has anyone seen anything like this. Any help would be appreciated.CheersLeo

Help with Plan Cache Query

Posted: 12 Jun 2013 12:29 PM PDT

I have had times when I would like to get the Query Plan from the Plan Cache.I have Query 1 below (MSDN) which give me the query text and last execution times but not the Query Plan.Or Query 2 below (also from MSDN) that give me the Query Plans but not the Last_execution times or other info like READ Write stats etc etcI am having to run a separate query to get this [SELECT * FROM sys.dm_exec_query_plan(plan_handle) ] using the returned Plan Handle.Is their any way to get everything in the one query? I have tried to cross apply sys.dm_exec_query_stats to Query 2 to give me the execution times but that was a guess and not right.thanks[b]Query 1[/b]SELECT dbid, sql_handle, (SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 , ( (CASE WHEN statement_end_offset = -1 THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2) ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement, execution_count, plan_generation_num, last_execution_time, total_worker_time, last_worker_time, min_worker_time, max_worker_time, total_physical_reads, last_physical_reads, min_physical_reads, max_physical_reads, total_logical_writes, last_logical_writes, min_logical_writes, max_logical_writes, plan_handleFROM sys.dm_exec_query_stats AS s1 CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2 WHERE s2.objectid is null ORDER BY s1.last_execution_time DESC[b]Query 2[/b]SELECT UseCounts, Cacheobjtype, Objtype, TEXT, query_plan,*FROM sys.dm_exec_cached_plans CROSS APPLY sys.dm_exec_sql_text(plan_handle)CROSS APPLY sys.dm_exec_query_plan(plan_handle)

Bakcup failure with fulltext catalog

Posted: 12 Jun 2013 09:18 PM PDT

Hi ,The fullbackup job failed with the below error :MessageExecuted as user: NT AUTHORITY \ SYSTEM. Allowed for a file or file group "XXXXXXX_event" for backup, because it is not online. You can use FILEGROUP or FILE clauses execute BACKUP, to limit the options include only online data. [SQLSTATE 42000] (Error 3007) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failedI chcked the fulltext catalog file is on online.please let me know how to resolve this issue ..Thanks,

Enabled LPIM and disk's latency went down to zero?

Posted: 12 Jun 2013 06:21 AM PDT

Hi,I wanted to start a short discussion about Lock Pages in Memory (LPIM) and any possible impact (positive one) on disk's latency.Recently, I contacted MS to get some help on a Cluster's issue. Long history short, the case is still open but it looks it has been resolved. However, they did recommend the use of LPIM on my SQL box.Now, I am running a standard version of SQL 2012 on a two node Cluster. The Cluster does not lack of RAM (96GB per node, with 64GB assigned to SQL, dedicated box) But just today and after a few days of that change, I noticed on my RedGate Monitoring tool, that read and writes (latency) on my Data LUN went down to zero! Could be that such action put most of my data into RAM, improving latency in such a dramatic way? Or ... should I look for any RedGate patch / issue? It's just hard to believe that a simple change, made such a huge improvement.I know how LPIM works, and that LPIM will avoid the Os to trim any memory from SQL server, etc, but I find these results really outstanding and dramatic, especially on a dedicated box.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog