DB Engine :: Fragmentation Percentage - Page Count Is 300 Only
Jan 22, 2015What is the impact of fragmentation when my tables fragmentation is 99% but page count is 300 only?
View 6 Replies
ADVERTISEMENT
SQL Server 2008 :: Fragmentation Versus Page Count
Jul 28, 2015I created a thread 2 days back on a performance problem RE non-trusted check constraints and foreign key constraints. We are planning to make them trusted to see if it works. On the other hand, we see about 50+ clustered/non-clustered indexes with >90% fragmentation but the page counts for all these indexes are in the range between 500-900. I'm reading in the whitepaper that index fragmentation with less than 1000 pages is not a concern.
View 7 Replies View RelatedIndexes Showing Fragmentation Percentage As 99%
Oct 20, 2015I have rebuilded indexes and some of the indexes still showing fragmentation percentage as 99%. I have cross checked page count for these indexes and they are more than 10000 pages.As i am aware if the page count is less we can ignore this percentage. But in my case the page count is 10000.
View 2 Replies View RelatedPercentage Of Fragmentation Increased After 6 Hours?
Aug 14, 2015Yesterday night, I rebuilded all indexes one a table since they were having high percentage of fragmentation . After rebuilding, I immediately checked the fragmentation percentage and it got reduced and I felt happy. However, when i saw the percentage of fragmentation after 6 hours, it increased from 1% to 48%. How to find what caused the increase and how to fix it.
View 36 Replies View RelatedDB Engine :: Fragmentation % Of Indexes Remain Same After Reorg
Oct 21, 2015I have an issue with fragmentation SQl server 2012 .I monitored indexes using sys.dm_db_index_physical_stats. fragmented % is still remain after re-organized the index.
View 9 Replies View RelatedRecord Count Percentage
Jul 19, 2007How do I write a query to give me the total records in databaseA.tableA divided by the total records in databaseB.tableA
This is what I have so far:DECLARE @today as datetime
DECLARE @past as datetime
/* set the dates */
set @today = getdate()
set @past = getdate() - 3 /* Go back 3 days */
/* create temp table for holding total cases*/
CREATE TABLE #CaseTotalCount
(
TotalCount int
)
/**/
CREATE TABLE #FlashCycleCount
(
FlashCount int
)
/**/
INSERT INTO #CaseTotalCount (TotalCount)
SELECT COUNT (DataBaseA.dbo.tblProcedureData.CaseID) as TotalCount
FROM DataBaseA.dbo.tblProcedureData
WHERE DataBaseA.dbo.tblProcedureData.Date < @today AND DataBaseA.dbo.tblProcedureData.Date > @past
/**/
INSERT INTO #FlashCycleCount (FlashCount)
SELECT COUNT (DataBaseB.dbo.tblFlashLog.FlashLogID) AS FlashCount
FROM DataBaseB.dbo.tblFlashLog
WHERE DataBaseB.dbo.tblFlashLog.LoadDate < @today AND DataBaseB.dbo.tblFlashLog.LoadDate > @past
/*function call*/
SELECT dbo.percentage(#FlashCycleCount.FlashCount, #PeriOpTotalCount.TotalCount)
FROM #FlashCycleCount
JOIN #PeriopTotalCount
ON #PeriOpTotalCount.TotalCount != 0 This works but when I try to bind this to a formview a get nothing. any ides?
Count And Percentage Of Skill Ratings
Jul 21, 2013I have a program which brings in Skill ratings for an assesmnet people with the title of "Worker have to take aolng with a file number they are assigned. The program also being in the reporting line that each worker is a part of.
SELECT distinct
o.VP,
o.AVP,
o.Director,
o.Supervisor,
o.Worker,
bs.File_NBR,
[Code] ....
The rating levels are 1 through 5. What I need to do is to do is create a table that shows the count and percentage of each rating giving to the workers for each skill grouped by Vp, AVP, Supervisor and Director. So all the works that are Ultimately under a AVP and all the wokers that are Ultimately under a director and so on.
NameRoleSkillCount of % of Count of % of
Rating 1 Rating 1 Rating 2 Rating 2
Gerald VPA1100 29% 13033%
Gerald VPB195 28% 9524%
Gerald VPB2120 35% 7018%
Gerald VPB330 9% 10025%
KimVPA1
KimVPB1
KimVPB2AND SO ON
[Code] ....
Column Count Percentage Of Not Null Fields
Jul 27, 2006Hello folks,
I am stuck at a problem, not sure on how to go about writing a query that will return as a percentage the number of fields in a row that are null.
For instance, a row from my table:
Row1 : field1 field2 field3
If field3 is empty or null, my query should return 67%.
So far I have gotten the number of fields:
select count(1) from information_schema.columns where table_name='myTable'
I could loop through the fields but I am sure there is a simpler way of doing it, I have seen something simpler in the past with some builtin SQL functions. I am using MS SQL 2005.
Thanks for your help
Mike
SQL Server 2012 :: Calculation Based On Count Function To Format As Decimal Value Or Percentage
Dec 4, 2014I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.
I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?
select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id
id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00
DB Engine :: Page Life Expectancy Above 100K
May 27, 2015I have few servers that are VM and different version of SQL Severs (2008 Express/Standard, 2012, or 2014 Enterprise) and seems all of them have same issue that when I am querying "sys.dm_os_performance_counters" for "[object_name] LIKE '%Buffer Manager%' AND [counter_name] = 'Page life expectancy'" I am getting very big number (Ex, above 100K or some servers 1M).Is this number seems fine or acceptable?Â
View 6 Replies View RelatedDB Engine :: How To Identify If TempDB Contention On Page Is PFS / GAM Or SGAM
Jun 23, 2015 I have used Extented event to monitor the occurances of TempDB contention on Production server . I found there are several entried logged in in 30 mints .Now I am trying to determin if Tempdb contention on PFS, GAM or SGAM page then I will decide if I need to increase the number of TempDB data files on Production server . Currently , There are 8 TempDB Data files configured on its separate Disks .There are Page_IDs I found in the extented events for Tempdb files -
Page_IDÂ =1 for PFS page
Page_ID = 2 for GAM page
Page_ID =3 for SGAM page
but I found the Below Page_IDs and I know there is a formula that you can use to identify if page is PFS,GAM or SGAM ? How should I use this formula and what should I look for to determine if page is PFS,GAM or SGAM ? Is there any threshold value for the duration of TempDB contention occured ?
Page 2 - Count
Jan 29, 2007very nice diagram
try this --
Code:
select T.title
, T.message
, ( select count(title_id)
from forum_subject
where title_id = T.title_id ) as Topics
, ( select count(title_id)
from forum_message
where title_id = T.title_id ) as Posts
, P.by_id as Last_post_by
, P.message_sdate as Last_post_date
from forum_title as T
left outer
join forum_message as P
on P.title_id = T.title_id
and P.message_sdate =
( select max(message_sdate)
from forum_message
where title_id = T.title_id )
DB Engine :: Page Life Expectancy Increases Dramatically From A Minute To Another
May 22, 2015I measure PLE on my server and insert them every minute into a table. Now, when I look into the table I just dont know how to interpret the following data. I dont understand how is that possible. It's an sql server bug? or? How to interpret that data?Â
View 10 Replies View RelatedSQL 2012 :: DB Engine Page Life Expectancy And Buffer Cache Hit Ratio Is Too Low
Sep 12, 2014Our server administrator forwarded some messages from SCOM that indicate:
SQL DB Engine 2012 Page Life Expectancy and Buffer Cache Hit Ratio is too low
When I logged into the offending server, I could not find anything in the SQL Log File that indicates this.
I was wondering how did SCOM identify this issue - where in SQL Server would this have been reported to SCOM?
How To Get Total Page Count For Individual Groups
Aug 3, 2007Hello,
I have report in which I have created groups base on the customer name. Can anybody please tell me how to get the total page counts for the individual group? I have page break after every new group and I am able to reset page count to 1 when new group start but I am getting the total number of pages for a particular group.
For ex, let say I have 4 groups, 1 group has 3 page, 2 group has 2 pages and 3 group has 6 page and 4th group has 7 pages I need something like,
For 1st group
'Page 1 of 3€™ when user click next page it should be 'Page 2 of 3' etc
Similarly for other groups as wll.
Thanks!
Group By Row Count And Force Page Break
Mar 23, 2007Hello All!
Is there a "clean" way to group rows from a table by the row count, and then force a page break each time the count hits a maximum number (say 25 rows)?
As an alternative, can you set the maximum count of rows in a table, and then force a page break if it reaches that count? (this may actually be a better solution for my needs).
Thanks!
Reporting Services :: All Record Are Displaying On One Page - How To Display Page By Page
Nov 11, 2015I have created one reports but all the records are displaying on one page.find a solution to display the records page by page. I created the same report without group so the records are displaying in page by page.
View 3 Replies View RelatedHow To Return SqlDataReader And Return Value (page Count) From SPROC
Jan 2, 2006This is my function, it returns SQLDataReader to DATALIST control. How
to return page number with the SQLDataReader set ? sql server 2005,
asp.net 2.0
Function get_all_events() As SqlDataReader
Dim myConnection As New
SqlConnection(ConfigurationManager.AppSettings("..........."))
Dim myCommand As New SqlCommand("EVENTS_LIST_BY_REGION_ALL", myConnection)
myCommand.CommandType = CommandType.StoredProcedure
Dim parameterState As New SqlParameter("@State", SqlDbType.VarChar, 2)
parameterState.Value = Request.Params("State")
myCommand.Parameters.Add(parameterState)
Dim parameterPagesize As New SqlParameter("@pagesize", SqlDbType.Int, 4)
parameterPagesize.Value = 20
myCommand.Parameters.Add(parameterPagesize)
Dim parameterPagenum As New SqlParameter("@pageNum", SqlDbType.Int, 4)
parameterPagenum.Value = pn1.SelectedPage
myCommand.Parameters.Add(parameterPagenum)
Dim parameterPageCount As New SqlParameter("@pagecount", SqlDbType.Int, 4)
parameterPageCount.Direction = ParameterDirection.ReturnValue
myCommand.Parameters.Add(parameterPageCount)
myConnection.Open()
'myCommand.ExecuteReader(CommandBehavior.CloseConnection)
'pages = CType(myCommand.Parameters("@pagecount").Value, Integer)
Return myCommand.ExecuteReader(CommandBehavior.CloseConnection)
End Function
Variable Pages is global integer.
This is what i am calling
DataList1.DataSource = get_all_events()
DataList1.DataBind()
How to return records and also the return value of pagecount ? i tried many options, nothing work. Please help !!. I am struck
Transaction Count After EXECUTE Indicates That A COMMIT Or ROLLBACK TRANSACTION Statement Is Missing. Previous Count = 1, Current Count = 0.
Aug 6, 2006With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False
'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i
' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
I Need To Pass Data Entered /created On The First Page To The Next Page And Populate The Next Page With Some Data From The Fir
Nov 28, 2006Hello I have a project that uses a large number of MS Data access pages created in Access 2003 and runs on MS SQL2005.
When I am on lets say my client, (first page in a series) data access page and I have completed the fields in the (DAP), I am directing my users to the next step of the registration process by means of a hyperlink to another Data access page in the same web but in a linked or sometimes different table.
I need to pass data entered /created on the first page to the next page and populate the next page with some data from the first page / table. (like staying on the client name and ID when i go to the next page)
I also need the first data access page to open and display a blank or new record. Not an existing record. I will also be looking to creata a drop down box as a record selector.
Any pointers in the right direction would be appreciated.
I am some what new to data access pages so a walk through would be nice but anything you got is welcome. Thanks Peter€¦
Fragmentation
May 13, 2008i want to do fragmentation using SQL 2005 Express. Can their be any helping material for making fragmentation. Any Example
View 2 Replies View RelatedFragmentation
Mar 29, 2001What DBCC or otherwise one would be able to find table fragmentation in SQL 7 and how to take some proactive actions ?
Thanks very much for your time.
-Tim
Fragmentation
Jun 1, 2004Would someone please let me know which is the better way to reduce fragmentation on Windows:NT drive?
dbcc indexdefrag
or
dbcc reindex
or
dropping and recreating indexes through SQL?
Thanks
Vinnie
Fragmentation?
Jul 27, 2007Sql Server 2000.
For simplicity, I am avoiding minor details.
Here is a scenario.
Create Table T (T_ID int PK, €¦..) and this table is referenced by 20 other places on T_ID.
And these 20 tables have T_ID as PK or part of PK.
We have large volume of deletes in T and in the other 20 tables based on T_ID.
Once the deletes are done, will dropping and re-creating the FK€™s that reference the T_ID
help reduce the fragmentation caused by the deletes?
Table Fragmentation
May 8, 2001hi all,
i have inherited a database running on sql7 sp2. a lot of the tables do not have a clustered index. some of these tables are highly fragmented. below is a sample of the dbcc showcontig output
TABLE level scan performed.
- Pages Scanned................................: 104934
- Extents Scanned..............................: 13124
- Extent Switches..............................: 13123
- Avg. Pages per Extent........................: 8.0
- Scan Density [Best Count:Actual Count].......: 99.95% [13117:13124]
- Extent Scan Fragmentation ...................: 5.84%
- Avg. Bytes Free per Page.....................: 7757.4
- Avg. Page Density (full).....................: 4.16%
this table has 5 non-null char columns whose total length is 43 chars and the table occupies about 900 mb for only about 670,000 rows!
select on the table sucks!
since there is no clustered index i have to rebuild the table to remove the fragmentation. is there a way to reorganize the table without recreating it?
thanx
:-)
hemant
Fragmentation -Urgent
Oct 13, 2000Hi!
I have a problem with defragmenting the tables. When i ran dbcc showcontig for the tables, it shows 94%, 50%, 33% fragmentations . I have rebuild indexes on all those tables and ran dbcc showcontig again but still it shows the same % of fragmentations. Is there any way to remove fragmentation on all the tables completely. Any help would be appreciated.
Thanks,
Saran
Disk Fragmentation && SQL
Apr 14, 2004One of my production servers has been determined to be 92% fragmented.
What's the proper procedure for defraging a database server?
I couldn't find anything very helpful in BOL, nor Knowledge Base.
Sidney Ives
Database Administrator
Sentara Healthcare
Disk Fragmentation
Aug 9, 2004What's the best way to find out if disk fragmentation on Windows 2000 Server is affecting SQL Server performance?
If disk fragmentation is shown to be a cause of performance problems, what are the recommendations for a disk fragmentation strategy? eg. use the win 2000 built in disk defrag utility or buy a 3rd party product like DiskKeeper? How much of an overhead is a product like DiskKeeper that defrags in the background?
Clive
Database Fragmentation
Oct 1, 2004Hello,
How can I mesure the database fragmentation ? Cause DBCC SHOWCONTIG shows obects fragmentation only. I would like to see the whole database fragmentation.
Thanks for help
Index Fragmentation
Jun 22, 2008What functions of tools do you use for managing index fragmentation?
DBCC?
I am working through MS Press SQL 2005 book and it mentions the
sys.dm_db_index_physical_stats function? It then give an example of code which is very involved.
Does anybody use this function?
Thanks
Index Fragmentation
May 10, 2007before rebuild index
---------------------
tablenameIndexname
avg_fragmentation_in_percent
Payoff_QuotePK_Payoff_QuoteDetail
83.3333333333333
ALTER INDEX ALL ON Payoff_Quote REBUILD
after issue the above statement result remain same.
Index Fragmentation
Jun 24, 2007Hi
When I run sys.dm_db_index_physical_stats on AdventureWorks.Production.Product, I get an average fragmentaion of 46%, 0%, 25% & 50% on the Indexes.
I run ALTER INDEX ALL ON AdventureWorks.Production.Product
REBUILD WITH (FILLFACTOR = 80, SORT_IN_TEMPDB = ON,
STATISTICS_NORECOMPUTE = ON)
and the index fragmentation does not change...
Why is that?? I have tried refreshing the table and the statistics but why does it look like rebuilding the indexes has done nothing?
Index Fragmentation In The DMV
Sep 25, 2007I have a nice script that will look at the index fragmentation by using the DMV (sys.dm_db_index_physical_stats) and if its above a specificed threshhold, it will rebuild or reorg the index.
What I have noticed is that even after reorging and/or rebuilding the index, the fragmentation percent in the DMV does not change for some indexes. Other indexes are updated just fine.
Is this a result of updating statistics? Why would the fragmentation change for some indexes and not others? Why does it seem no matter how much rebuilding or reorging is done, the fragmentation percent for some indexes does not change?
- Eric