An evaluation of whether a system works well enough to be fit for purpose. Normally performance refers to the speed with which a system completes an operation or set of operations over time.
7
votes
4answers
1k views
Help me choose a RAID level combination for a SQL Server 2008 instance
I am going to rebuild one IBM 3400 server from scratch. This server is dedicated to a SQL Server 2008 instance running on Windows 2008 R2.
I am going to make new RAID configuration. I have 6 SCSI ...
2
votes
1answer
286 views
Can I choose which CPUs / Cores to be used by MySQL?
I have a Windows VPS (Windows 2008 R2 x64) that runs a couple WordPress blogs and other few ASP.NET things. The VPS runs 2 CPUs (for example).
With ASP.NET, in such situation, I usually set the app ...
2
votes
1answer
252 views
Pros and Cons of having additional columns in join table of many to many relationship
I searched but did not find any discussions on this topic.
Are there any pros and cons on creating many extra columns on joined table?
Does it affect speed of data retrieval because we are joining 3 ...
1
vote
1answer
253 views
How to prevent big performance impact during index creation
I have an existing database that is missing some indexes. When I tried creating some the database server really suffered in CPU usage during index creation interfering with the regular workload. Is ...
3
votes
3answers
97 views
Is it much more expensive to use procedures that get lists and deriving a count in the application vs. a separate stored procedure for the count?
I would like to know if returning a count is much more expensive than returning a dataset in regard to processing performance? I assume there is a bandwidth increase in delivery to the application ...
4
votes
1answer
1k views
How can I monitor the call count per stored procedure per timespan?
In order to diagnose some performance issues I would like to get a better understanding of the number of times certain procedures are called compared to the system performance. Is there a way to get ...
11
votes
2answers
322 views
To maximize DB performance, which commands should be run after loading large amounts of data into SQL Server 2008 via SSIS
I have written an SSIS package to load test data into an empty database. Some of the tables are very large (~700 million rows). Once the SSIS package has completed are there any commands I should run ...
5
votes
1answer
593 views
Password hashes: Fixed-length binary fields or single string field?
I'm currently having am amusing debate with a friend, and we simply can't agree on the best method to store salted passwords in a database. The two options on the table are:
Storing the hash and the ...
6
votes
5answers
2k views
Tools to monitor Oracle Database Performance on Standard Edition
Here is what I am looking for:
Works on Standard Edition i.e. No Diagnostic Pack.
Provides both current and historical performance data.
Good visuals for easy spotting of trends and presentation ...
3
votes
1answer
1k views
Why does MySQL use all of memory and goes into swap when doing lots of delayed inserts?
We have a database server running Centos 5.6 64bit 64Gb of RAM. The server is idle most of the time. However, every hour it gets a lot of DELAYED inserts. Up to 3 mil INSERTs per hour into a MyISAM ...
3
votes
1answer
1k views
What is the usage of performance_schema in MySQL 5.5?
I have upgraded to MySQL version 5.5. There, I had observed performance_schema in the schema list.
What is the usage of this new schema..?
How can we monitor using this schema..?
What are the ...
13
votes
3answers
4k views
Efficient INSERT INTO a Table With Clustered Index
I have a SQL statement that inserts rows into a table with a clustered index on the column TRACKING_NUMBER.
E.G.:
INSERT INTO TABL_NAME (TRACKING_NUMBER, COLB, COLC)
SELECT TRACKING_NUMBER, COL_B, ...
2
votes
1answer
342 views
Unicode support of PostgreSQL and its performance
How is the Unicode support of PostgreSQL?
If I want to save Unicode data does it have to go through the conversion process while storing and retrieving? If yes then what is the performance of such ...
1
vote
1answer
489 views
Set statistics time showing different elapsed_time than dm_exec_query_stats view
When I exec a SQL statement after setting set statistics time on and running dbcc freeproccache and dbcc dropcleanbuffers I get the following output:
DBCC execution completed. If DBCC printed error ...
7
votes
5answers
1k views
What could be causing strange query timeouts between PHP and MySQL?
I am the senior developer on a Software-as-a-Service application used by many different customers. Our software runs on a cluster of Apache / PHP application servers, powered by a MySQL backend. On ...
1
vote
1answer
155 views
Appropriate Windows version for Mysql
I have a HP ProLiant DL380 G5 server and I would like to install mysql on it .
My questions are:
Which MySQL version should I install from this link for better performance, 32 bit or 64 bit?
Which ...
3
votes
3answers
3k views
How to avoid index scans in SQL Server 2005?
I have a table named Cell which has columns like Cell_ID, CellValue and CellRow.
Cellrow can have values maximum 1 to 5. There is a clustered index on Cell_ID field. This table contains 100000 rows.
...
11
votes
4answers
2k views
Does restarting SQL Server speed it up?
I have noticed that some DBAs restart SQL Server very frequently, sometimes even nightly. I believe they do it to free up some memory, or perhaps to speed up queries too. I know that after a restart ...
5
votes
2answers
1k views
What are the potential causes for a sudden MySQL slowdown?
A few days ago mysql became suddenly very slow with queries taking more than 10s to complete when they used to complete under 100ms... It lasted about an hour and then the speed was normal again. What ...
3
votes
2answers
530 views
Website is too slow when number of records increases?
I have developed a web application called Referral Program, It it developed by using PHP, MySQL. Users can joining the website and when they introduce new members, he/she gets a commission. The system ...
0
votes
1answer
231 views
How could I handle heavy load and consistency for product inventory in online retailers? [closed]
I have posted that question on Stackoverflow and they suggest me to repost my question here, as it would more likely to get help from a DB community (regarding this issue).
Hope you guys don't mind.
...
2
votes
1answer
972 views
Is SQL Server Backup single or multi-threaded?
If you perform a...
BACKUP DATABASE [DB_Name] TO DISK=N'c:\backups\db_name.bak' WITH STATS=10
Will that perform a single or multi-threaded write?
Our Server support company advised us that if we ...
1
vote
2answers
723 views
How to troubleshoot SQL “The timeout period elapsed prior to completion of the operation or the server is not responding.”
I'm trying to troubleshoot a timeout issue that occurs on one of our SQL 2005 servers.
The database responds normally for about a day until we start to see sql timeout errors. After that all ...
12
votes
5answers
438 views
Indexing from start or when performance problem arises?
My question is regarding use of indexes.
Should I start indexing right from the start or when performance problem arises?
We can also create temporary index while executing a query. What are the ...
12
votes
3answers
818 views
Slow deletion of records when a trigger is enabled
Thought this was solved with the link below - the work around works - but the patch doesn't. Working with Microsoft support to resolve.
http://support.microsoft.com/kb/2606883
Ok so I have an ...
3
votes
1answer
109 views
What units is this metric in?
Im currently using the following query to get the Page Life expectancy from sql server:
declare @PLE int
select @PLE=cntr_value from psql1.master.sys.dm_os_performance_counters where ...
6
votes
2answers
480 views
Database locking issues?
I have run into a transaction related issue on a SQL Server 2008 production database. A brief overview is that we have a website that has numerous concurrent users around the state, who do GUI type ...
2
votes
2answers
200 views
Improving the performance of a long query
I have a query that i have let run for a few hours. I am forced to kill the query because it is taking up 60% of the CPU. Is there anything i can do to improve its performance?
select ...
1
vote
3answers
139 views
SQL Server Express acting strange
I'm not an IT pro, but a web developer that happens to have his own blog.
The blog is hosted on a private VM on which I have both IIS and SQL Server.
Sometime my blog, which is not super busy stops ...
2
votes
1answer
516 views
SQL Server Replication causing locks on table
we have three MSSQL 2005 servers (database is set to compatibility level 80 for legacy reasons) which we have just set up Transactional Replication w/ Updateable Subscriptions on. Two which are ...
3
votes
3answers
267 views
Same tables with same data, different SQL Server stored procedure performance
I have 2 SQL Server 2008 instances installed on the same server. They have same table structures with same data. I have a stored procedure deployed on both of them. The stored procedure performs ...
1
vote
1answer
242 views
MySQL Performance Planning
I am considering moving my existing script which is downloaded to be a hosted script. One of the things it does is to keep track of some stats. My concern is with heavy hitters (100,000 ...
7
votes
4answers
571 views
DDL contention on TempDB
I have a SQL Server 2005 Standard x64 that is experiencing issues with TempDB DDL contention for the past few months. The server will experiencing contention on wait resource 2:1:103 (the wait type ...
3
votes
3answers
3k views
Best of MyISAM and InnoDB
Is it possible to make InnoDB to use indexes same as MyISAM instead of clustered index due to limitation of RAM while getting benefit of its concurrency performance?
4
votes
2answers
689 views
Database design for dynamic Meta fields of an entity that shall be queried intensively
I'm designing a database structure for a system at which an entity can have a dynamic set of user-defined meta attributes. The meta fields (e.g.: popularity, conversion etc.) and skill values (int, ...
10
votes
1answer
10k views
Multi cores and MySQL Performance
Importance of RAM is an established fact but far less material is available about the importance of cores and multithreading when it comes to the usage of CPU by MySQL. I am talking about the ...
4
votes
2answers
208 views
What is the consensus on using NOT NULL constraints on all text-based fields in a table?
Some of the tables I have come across don't have any NOT NULL constraints, which has yielded to some gotchas when querying.
I'm curious what the feelings are with using something like NOT NULL ...
3
votes
2answers
89 views
Should fields that aren't needed often be kept in separate tables?
I have a tagging system on my site. I also want to add descriptions to the tags just like on the S.E. sites.
The tags are used for a lot of stuff. Its how categories know what products to display, ...
6
votes
3answers
808 views
Testing stored procedure scalability
I have an email application that will be called upon to deliver to the UI the number of new messages for a given user on each page load. I have a few variations of things I am testing on the DB level ...
3
votes
3answers
809 views
When do hash indexes become reasonable?
MySQL natively doesn't support hash indexes. So, making a pseudo hash column and creating an index on a hash column needs some thinking.
And it seems to be widely understood that if the text field is ...
4
votes
3answers
704 views
For every insert statement I see sp_executesql in SQL Server Profiler
Working on tuning our database and queries for one of our products, I decided to pop open Profiler in the QA environment and see what it shows.
I saw for every insert I called, a call was made to ...
1
vote
1answer
388 views
What is com_set_option?
As the title says, I'm wondering what is this command and why am I seeing a large amount of this query on my database? It's currently 13.30% of my total queries. I've tried googling this but there's ...
2
votes
2answers
177 views
Differences between development and live database setups
I was wondering if there are any best practice recommendations on setting up databases in a dev environment versus a live environment with regards to configuration.
Should they be as similar as ...
16
votes
3answers
16k views
Possible to make MySQL use more than one core?
I've been presented with some dedicated MySQL servers that never use more than a single core. I'm more developer then DBA for MySQL so need some help
Setup
The servers are quite hefty with an ...
2
votes
1answer
453 views
Replication slows down on massive deletes
First of all, I'm not a DBA, but I'd like to get advice how to deal with the following issue.
We have few database servers based on SQL Server 2008 R2 SP1. They have been organized as shown below, so ...
8
votes
2answers
2k views
How to know when/if I have too many indexes?
Running Microsoft SQL Server Profiler every now and then, it suggests me with a bunch of new indexes and statistics to create ("...97% estimated improvement...").
From my understanding every added ...
11
votes
2answers
2k views
Is nested view a good database design?
I have read somewhere long time ago. The book states that we should not allow to having a nested view in SQL Server. I am not sure the reason why we can't do that or I might remember incorrect ...
3
votes
2answers
146 views
How can a key lookup with zero executions and zero actual rows take time?
I have a query that is taking about 10 seconds to run and I am trying to optimize it.
All the time is spent in the key lookup which is fine however in this case if I mouse over the key lookup in the ...
2
votes
2answers
475 views
SQL 2005- stored procedure performance troubleshooting
I am investigating performance issues for a stored procedure on SQL 2005 server.
This is how I intend to do it:
Analyze the stored procedure for any bad TSQL practices (like count * etc)
Run it ...
4
votes
3answers
330 views
Usage of SQL Server 2008 R2 Express. Please Advise
I'd like you all to give me hints and advise on the following scenario:
We are considering using Microsoft's SQL Server 2008 R2 Express for a business Web Application.
The Web Application won't have ...
