SQL Server 2008 :: Fragmentation Versus Page Count
Jul 28, 2015
I 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
ADVERTISEMENT
Jan 22, 2015
What is the impact of fragmentation when my tables fragmentation is 99% but page count is 300 only?
View 6 Replies
View Related
Oct 15, 2015
I have few queries.
1. Is there any way to get the Table Row Count if we are not maintaining the count.
2. This is for Update statistics
a. Should we run update statistics in our database for all the tables? The database is highly transactional.
b. How should i calculate the sample size that will suite for all the tables.
There are some tables which gets reindex, this we will ignore. We have a job, which reorganise some tables. Now the decision need to be taken what table should we update statistics to:
1. The table which has been reorganised
2. Table which has its statistics outdated.
View 2 Replies
View Related
Jun 12, 2015
I want to import xml file directly from web page into microsoft sql table. At the moment the import is done after the XML file is downloaded local.I want to skip this step to manualy download the file.It can be done in SQL? when i change the path i get this error: Cannot bulk load because the file URL... could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.)
below is the code
DECLARE @idoc INT
DECLARE @doc XML
SET @Doc = (SELECT * FROM OPENROWSET(BULK 'F:Folderbrfxrates.xml', SINGLE_CLOB) AS xmlData) -- 1 LOCAL works
--SET @Doc = (SELECT * FROM OPENROWSET(BULK 'http://www.bnr.ro/nbrfxrates.xml', SINGLE_CLOB) AS xmlData) -- from web i get error
SELECT @Doc
[code]...
View 0 Replies
View Related
Jun 21, 2007
I've been using full-text for quite some time, but I switched to another product for quite some time. The reason is that microsoft full-text doesn't offer the feature where one can get a resultset of page x out of y pages, i.e: give me top 100 matches of page 125 out of 5000 pages. I disappointedly waited for SQL Server 2005 and now it doesn't seem to support in sql server 2008 full-text search either. I had to get all 5000 pages of data, then jumped to page 125 to get just 100 records of that page--had to go get a coffee and came back for a 2+ millions rows table. Customers want the result in a split of a second.
Anyone knows if the final version of sql server 2008 will have better support for paging and getting top x rows? I scanned through 2008 BOL and didn't see any change from sql server 2005 or even sql server 2000 full-text search. (one improvement in sql server 2005 over sql server 2000 was the speed of populating large catalog and multiple languages support)
Thanks,
HH
View 1 Replies
View Related
Sep 9, 2015
I created a report where collected payments are listed by each collector from month to date.Everything Looks ok on the reporting side until a requirement came up where I am Also supposed to list the total number of payments collected by each collector (the count) and also the (total sum). If you look at the attached excel you would see two tables, I am getting the first table, 2nd is the one I want expected). How do I perform sum and count operations?
ALTER PROC USP_PaymentsByCollector
(
@CollectorName AS varchar(20) = NULL,
@CollectorCode AS varchar(20) = NULL,
@LoanID AS varchar (20) = NULL
--@StartDate AS DATETIME = NULL,
----@EndDate AS DATETIME = NULL
[code]....
View 3 Replies
View Related
May 11, 2015
I am working now on optimization of an update query for a particular table and I want to measure the number of page splits after each update. How to check it?
View 6 Replies
View Related
Oct 16, 2015
I have moved some DB's from a physical server to a virtual server while the physical server is being upgraded. This morning when I checked to make sure all the jobs are running fine I saw that a backup job failed for one of the databases. The error was :
[color=#ff0000]Executed as user: NT AUTHORITYSYSTEM. BACKUP 'database 1' detected an error on page (1:1400911) in file 'D:Datadatabase 1.mdf'. [SQLSTATE 42000] (Error 3043) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.[/color]
Then I ran a DBCC check db command and found the following errors:
Msg 8928, Level 16, State 1, Line 1
Msg 8939, Level 16, State 98, Line 1
Msg 8976, Level 16, State 1, Line 1
Msg 8978, Level 16, State 1, Line 1
Msg 8939, Level 16, State 98, Line 1
Msg 8978, Level 16, State 1, Line 1
The messages varies from data from one page cannot be referenced on another, data page cannot be processed, page was not seen in the scan although the parent was etc etc.
View 9 Replies
View Related
Oct 29, 2015
This is the scenario in my environment
WaitType Wait_Sec Resource_Sec Signal_Sec Wait Count Wait Percentage
WRITELOG920039.89887485.89 32554.00 23446032975.02
View 9 Replies
View Related
Apr 23, 2015
SET NOCOUNT ON
Declare @daysOld int,@deletedate nvarchar(19) ,@strDir varchar(250)
declare @cmd11 nvarchar(2000)
declare @mainBackupDir varchar(2000),
@result1 nvarchar(max);
[Code] ....
View 1 Replies
View Related
May 27, 2015
My data has 2 fields: Customer Telephone Number, Date of Visit.
Basically I want to add a field ([# of Visits]), which tells me what number of visit the current record is within 6 months.
Customer TN | Date of Visit | # of Visits (Within 6 month - 180 days)
1111 | 01-Jan-2015 | 1
1111 | 06-Jan-2015 | 2
1111 | 30-Jan-2015 | 3
1111 | 05-Apr-2015 | 4
1111 | 07-Jul-2015 | 3
As you can see, the last visit would counts as 3rd because 180 days from 07-Jul-2015 would be Jan-8-2015.
View 3 Replies
View Related
Oct 7, 2015
Naming convention and what am I doing wrong here:
,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoIDError message:
Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.
View 9 Replies
View Related
Jul 16, 2015
We noticed a deadlock 3-4 weeks ago on a table (table1) and deadlock graph was captured.
When I am analyzing the deadlock graph, page number using DBCC PAGE, I am getting the object id for a different table (table2). But deadlock graph shows the name of the object as table1.
Is it possible that subsequent defragmentation of indexes would have caused the respective page id to got re-allocated to a different table? I checked the deadlock graph lately only after 3-4 weeks.
View 1 Replies
View Related
Apr 7, 2004
Hi
We are facing an acute situation in our web-application. Technology is ASP.NEt/VB.NET, SQL Server 2000.
Consider a scenario in which User 1 is clicking on a button which calls a SQL stored procedure. This procedure selects Group A of records of Database Page1.
At the same time if User 2 also clicks the same button which calls same SQL stored procedure. This procedure selects Group B of records of Database Page1.
So, its the same Page1 but different sets of records. At this moment, both the calls have shared locked on the Page1 inside the procedure.
Now, in call 1, inside the procedure after selecting Group A of records, the next statement is and update to those records. As soon as update statement executes, SQL Server throws a deadlock exception as follows :
Transaction (Process ID 78) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction
We are able to understand why its happening. Its because, Group A and Group B of records are on the same Page1. But both the users have shared lock on the Page1. So, no one gets the exclusive lock in records for update, even though, the records are different.
How can I resolve this issue? How can I get lock on wanted rows instead of entire page?
Please advice. Thanks a bunch.
Pankaj
View 1 Replies
View Related
Jan 29, 2007
very 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 )
View 11 Replies
View Related
Mar 13, 2015
I've written a custom script to delete backup files from location. But unable to modify now to count the number of files are deleted. How to modify the script...
/* Script to delete older than N days backup from a specific directory */
USE [db_admin]
GO
IF OBJECT_ID('usp_DeleteBackup', 'P') IS NOT NULL
DROP PROC usp_DeleteBackup
GO
[Code] .....
View 2 Replies
View Related
Aug 3, 2007
Hello,
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!
View 3 Replies
View Related
Mar 23, 2007
Hello 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!
View 7 Replies
View Related
Dec 7, 2006
Abe writes "i am using SQL server 2000, when i tried to back up the database in SQL server, the backup began failing coz SQL Server could no longer allocate 964kb of contiguous blocks. (the min needed is 1MB)
What causes the memory or blockes to be fragmented?
How do you solve this issue other than rebooting the server?
How do you prevent this from happening or monitor this issue?
Regards,
Abe"
View 1 Replies
View Related
Jun 8, 2000
Hi!,
I have a process(Peoplesoft) in SQL Server which takes long time to finish. I am looking for is there any way to find out fragmentation of the table to defagment it or is there any way to allocate the size for particular table. Couple of users are running the process at same time but as SQL Server has table/page locks it locks and releases after the job is done. Can i make it row level lock by executing sp_tableoption procedure. I would appreciate for all your suggeations.
Thanks,
Mohana
View 4 Replies
View Related
Nov 19, 2015
I have a SSRS report with data in below format.
ID      Name   Date
1Â Â Â Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â null
2Â Â Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â 01/01/2012
3Â Â Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â 01/02/2013
Also, I have a sort parameter @sort and values are (Name, ID, Date)
I want to apply page break whenever @sort=Name. There should be no page break when user selects @sort = ID or Date. Page break should happen only when @sort value = Name
it should be like this...
Page 1:
ID      Name   Date
1Â Â Â Â Â Â Â Â Â Â AÂ Â Â Â Â Â Â Â null
Page 2:
ID      Name   Date
2Â Â Â Â Â Â Â Â Â Â BÂ Â Â Â Â Â Â Â 01/01/2012
Page 3:
ID      Name   Date
3Â Â Â Â Â Â Â Â Â Â CÂ Â Â Â Â Â Â 01/02/2013
I need achieving the above task.
View 2 Replies
View Related
Dec 1, 2014
I have bunch of heap tables and the fragmentation seems to be high, i am not sure whether i shall add index for them, as these tables are inserted and updated every day.
View 4 Replies
View Related
Apr 26, 2015
We have a database with a table that contains around 180m records. Each day a further 70k are inserted. No records are ever deleted as this table is used for archiving only.Users are required to perform SELECTs on this table constantly but due to the high number of INSERTs the indexes become very fragmented very quickly. My aim is to avoid daily rebuilds of the indexes which is what our software house is telling us we have to do.
This is the DDL for the table:
CREATE TABLE [dbo].[Inventory](
[EAN] [bigint] NOT NULL,
[Day] [smalldatetime] NOT NULL,
[State] [int] NOT NULL,
[Quantity] [int] NULL,
[StockValue] [float] NULL,
CONSTRAINT [PK_Inventory] PRIMARY KEY CLUSTERED
[code]...
There are also three clustered Indexes on this table each referencing a single column. The problem from my side is that I cannot understand why the three columns in a primary key would also be configured as non-clustered indexes.My solution would be one of the following:
1. Accept the tables are going to be fragmented and require a daily rebuild (don't like this one!)
2. Partition the table
3. Remove the non-clustered Indexes and let the clustered index for the primary key do the work.
View 9 Replies
View Related
Oct 25, 2000
Hi all,
Anyone here ever used the Informix database and can give me some differences between Informix and SQL.
One of our users is thinking about purchasing a COTS product that only supports an Informix database. I need to convince the user to evaluate other rival applications that can support SQL and need some arguments in favor of not going with Informix.
Any ideas appreciated,
Faustina
View 6 Replies
View Related
Dec 14, 2006
Hi all,
we are about to purchase new database servers and have been offered a good deal on 64-bit Xeon machines. At present we run SQL 2000 on Windows Server 2003 both of which are 32-bit versions.
Is there any problem using our current 32-bit Server software on the 64-bit machines (apart from not being able to utilise its full power)? I'm assuming the SQL 2005 licenses are the same price regardless of 32-bit or 64-bit version. If we buy a 64-bit SQL Server version license are we going to get the best out of it on a 32-bit Windows Server edition?
tia - Matt
View 1 Replies
View Related
Sep 21, 2006
Hi, I wanted to know that can we access a webpagefrom a store procedure in sql server 2000 like we run a exe file from sql server. Bye.
View 1 Replies
View Related
Mar 12, 2015
I have a report with tablix. when tablix returns no rows Footer is coming all the way up . How to display the footer at the bottom of the page all time.
View 0 Replies
View Related
Jun 18, 2007
If there is someone who is intimate with both sqlserver and oracle tell me the main differences between the two?
what features are not available in either one? is one better for certain situations over others?
View 5 Replies
View Related
May 25, 2007
Hi there,
Don't know if anyone can help,
There used to be a Whitepaper on the Microsoft website comparing SQL server (2000?) against Pervasive SQL or 2000, the link was http://www.microsoft.com/technet/treeview/default.asp?url=/technet/prodtechnol/sql/.
But it's not working anymore.
Does anyone have a copy of this paper, or know where it's now located? I've been looking for hours and can't find it.
Thanks in advance.
Jezza
View 3 Replies
View Related
Mar 21, 2008
hi guys,
I am joining IT industry in august,my main intrest is in database(readed some stuff of warehousing oracle 9i and sql).
However to move my career in that direction i need certification.
So here is the point,should i get MCTS(in sql server 2005) or OCP(oracle associate 10g).
My main goal is to become DBA. which of the above is more benificial.
I mean more oppurtunites.
please reply.
View 4 Replies
View Related
Mar 16, 2007
I have this posted in the VFP section of the forums but the more I find out about the issue the more I think there is an SQL Server security issues.
What I am doing is trying to connect to a LinkedServer of my SQL Express 2005 database running on windows vista. I have the linked server set up to connect to a local FoxPro table using the VFPOLEDB provider. If I log in as the 'sa' account I am able to do my query. But if I log into the server using a trusted connection it doesn't work. I get the error [The OLE DB provider "VFPOLEDB" for linked server "sys" reported an error. The provider did not give any information about the error.]
So I went in and set the BUILTINUser login as the sysAdmin. I also made sure that all of the security settings in SQL server were set up the same between the trusted user and the 'sa' user.
My question is, What am I missing? Is there something that I need to set on windows or is there a setting in SQL server?
View 2 Replies
View Related
Feb 7, 2008
Hi,
I'm using ComponentArt's Callback grids with Manual Paging.
The CA example grid uses Access:(http://www.componentart.com/webui/demos/demos_control-specific/grid/programming/manual_paging/WebForm1.aspx)
That SQL syntax produced is invalid in SQL Server 2005.
Example:
"SELECT TOP " & Grid1.PageSize & " * FROM (SELECT TOP " & ((Grid1.CurrentPageIndex + 1) * Grid1.PageSize) & " * FROM Posts ORDER BY " & sSortColumn & " " & sSortOrderRev & ", " & sKeyColumn & " " & sSortOrderRev & ") ORDER BY " & sSortColumn & " " & sSortOrder & ", " & sKeyColumn & " " & sSortOrder
So...This is what I have (simplified), and it appears return incorrect rows on the last few pages:
SELECT top 15 * FROM Posts where & sFilterString & " and Postid in (SELECT TOP " & ((Grid1.CurrentPageIndex + 1) * Grid1.PageSize) & " Postid FROM Posts where " & sFilterString & " ORDER BY " & sSortColumn & " " & sSortOrder & ") " & " ORDER BY " & sSortColumn & " " & sSortOrderRev
What other approaches has anyone used besides the "ID in (...)"?The examples I have included show the available variables: sort asc and desc, current page, number of rows on a page, etc.
View 2 Replies
View Related
Apr 30, 2007
Hello All,
I would like to compare the MSCS with Polyserve's solution and thus wondering if anyone has an experience using both or knows -
What are the major differences between the two?
What are the limitations?
What are the advantages and disadvantages?
Which is advisable to use as a HA solution?
Any other information which may help me to make this decision would be appreciated.
Thanks in advance for your help!
~Managar
View 1 Replies
View Related
